CODEWITHSUNDEEP
sqlt-sqlsql-server-2008viewsubquery
celsowm
celsowm

Reputation: 404

How work with subquery in a View?

I need create a View for this Query, allowing change m.arq_data and m2.arq_data values when a select uses this View, like:

select * FROM the_view WHERE m.arq_data = X AND m2.arq_data = Y

Here is my current query:

SELECT distinct(p.num_processo),p.num_proc_jud,a.assunto,su.subassunto,ma.Materia,u.Unidade ,M.COD_UNIDADE as cod_serv ,u.Unidade as servidor  ,' '  as serv_ativo,
'  '  as data_vinc ,' '  as  V_ativo, M.motivo,M.data_movimentacao as data_mov_distr ,
(select max(m2.arq_data)  from movimentacao m2 where  m2.arq_data 
BETWEEN '2011-08-01 00:00:00'AND '2011-08-31 23:00:00' and m2.num_processo =
  p.num_processo) as Data_Arq_Desarq , status = 'A'

--pra view
, M.arq_data

FROM processo p 
INNER JOIN assunto a ON a.cod_assunto  = p.cod_assunto  
INNER JOIN subassunto su ON su.cod_subassunto  = p.cod_subassunto
LEFT  JOIN materia ma ON ma.cod_materia  = p.cod_materia 
inner JOIN movimentacao M on M.num_processo = p.num_processo
INNER JOIN Unidade u ON u.cod_unidade  = M.COD_UNIDADE  
where 
not exists(select * from anexos a where a.num_proc_anexo = p.num_processo and a.ativo = 1) 
and not exists(select * from movimentacao m1 where m1.num_movimentacao= M.num_movimentacao and m1.motivo = 10 and m1.arquivado = 0) 
and ( not exists (select  * from distrib_vincjud  d2 where d2.num_processo = p.num_processo) 
or  p.num_processo in  (select  d3.num_processo from distrib_vincjud  d3  
                         where d3.num_processo = p.num_processo 
                         and d3.cod_servidor not in(select cod_servidor from servidor)
                         and d3.id_vinc in (select  max(d4.id_vinc) from distrib_vincjud d4 
                  where d4.num_processo = d3.num_processo and d4.data_vinc <= M.arq_data )))
and M.COD_UNIDADE in (select M.COD_UNIDADE   from movimentacao m2 where 
(m2.cod_ORIGEM_MOV = '26000181' or m2.cod_ORIGEM_MOV = '2600000X')and m2.num_processo = p.num_processo)
and p.tipo = 'J'
and  M.arq_data  >= '2011-08-01 00:00:00'AND M.arq_data  <='2011-08-31 23:00:00'

Upvotes: 0

Views: 90

Answers (2)

Abe Miessler
Abe Miessler

Reputation: 85046

If you are saying you want to define your view to work with changing dates for conditions like m2.arq_data BETWEEN '2011-08-01 00:00:00'AND '2011-08-31 23:00:00' then it depends what your requirements are.

For instance if this date range will always be between today and 31 days from now then you could change this line to be something like this:

m2.arq_data BETWEEN GetDate() AND DATEADD (dd, 31, GetDate())

If the date range is not something fairly simple that can be defined generically using SQL then you might want to consider using a stored proc or a udf and passing it the date params.

Upvotes: 1

a1ex07
a1ex07

Reputation: 37354

View cannot take parameters. Implement it as table-valued udf instead (or stored procedure).

Upvotes: 1

Related Questions