Jump to content
Sign in to follow this  
asacap1000

Consulta utilizando variáveis dentro do Between

Recommended Posts

Galera estou aqui mais uma vez, montei um select para gerar um relatório por período, tenho uma tela em php que utilizo para colocar as datas, "de" "até". Assim eu utilizo duas variáveis, $termo2 e $termo4. Meu select é bem grande com vários subs.e Dentro destes subs as datas precisar estár com estas variaveis. por exemplo: where nfe.data between '01-mai-2014' and '31-mai-2014'.

 

Como eu faria para adicionar as variáveis neste campo?

Abaixo o select completo.

select nf.dochd_own_ent_id, cl.entdf_dsc as cliente,
       nvl((select sum(it2.docsv_amt)
              from dochd nf2, docsv it2
             where nf2.dochd_iss_dt between '01-mai-2014' and '31-mai-2014'
               and nf.dochd_own_ent_id  = nf2.dochd_own_ent_id
               and nf2.dochd_st         = 91
               and nf2.dochd_doc_prc_tp = it2.docsv_doc_prc_tp
               and nf2.dochd_doc_prc_id = it2.docsv_doc_prc_id
           and ltrim(rtrim(it2.docsv_cd)) in ('AUR200','AUR001','01.02','01.01','01.03','301.03')),0) as Armazenagem,
       nvl((select sum(it2.docsv_amt)
              from dochd nf2, docsv it2
             where nf2.dochd_iss_dt between '01-mai-2014' and '31-mai-2014'
               and nf.dochd_own_ent_id  = nf2.dochd_own_ent_id
               and nf2.dochd_st         = 91
               and nf2.dochd_doc_prc_tp = it2.docsv_doc_prc_tp
               and nf2.dochd_doc_prc_id = it2.docsv_doc_prc_id
               and it2.docsv_cd         in ('AUR002','02.02','02.01','02.03','302.03')),0) as Ger_risco,
       nvl((select sum(it2.docsv_amt)
              from dochd nf2, docsv it2
             where nf2.dochd_iss_dt between '01-mai-2014' and '31-mai-2014'
               and nf.dochd_own_ent_id  = nf2.dochd_own_ent_id
               and nf2.dochd_st         = 91
               and nf2.dochd_doc_prc_tp = it2.docsv_doc_prc_tp
               and nf2.dochd_doc_prc_id = it2.docsv_doc_prc_id
               and it2.docsv_cd         in ('AUR094','07.01','07.04','03.01','03.11','03.02','03.04')),0) as Moviment,
       nvl((select sum(it2.docsv_amt)
              from dochd nf2, docsv it2
             where nf2.dochd_iss_dt between '01-mai-2014' and '31-mai-2014'
               and nf.dochd_own_ent_id  = nf2.dochd_own_ent_id
               and nf2.dochd_st         = 91
               and nf2.dochd_doc_prc_tp = it2.docsv_doc_prc_tp
               and nf2.dochd_doc_prc_id = it2.docsv_doc_prc_id
               and it2.docsv_cd         in ('03.03','303.03','AUR029','03.13')),0) as uni_des,
       nvl((select sum(it2.docsv_amt)
              from dochd nf2, docsv it2
             where nf2.dochd_iss_dt between '01-mai-2014' and '31-mai-2014'
               and nf.dochd_own_ent_id  = nf2.dochd_own_ent_id
               and nf2.dochd_st         = 91
               and nf2.dochd_doc_prc_tp = it2.docsv_doc_prc_tp
               and nf2.dochd_doc_prc_id = it2.docsv_doc_prc_id
               and it2.docsv_cd         in ('04.01','AUR004','04.03')),0) as Pesagem,
       nvl((select sum(it2.docsv_amt)
              from dochd nf2, docsv it2
             where nf2.dochd_iss_dt between '01-mai-2014' and '31-mai-2014'
               and nf.dochd_own_ent_id  = nf2.dochd_own_ent_id
               and nf2.dochd_st         = 91
               and nf2.dochd_doc_prc_tp = it2.docsv_doc_prc_tp
               and nf2.dochd_doc_prc_id = it2.docsv_doc_prc_id
               and it2.docsv_cd         in ('05.03','05.02')),0) as averbacao,
       nvl((select sum(it2.docsv_amt)
              from dochd nf2, docsv it2
             where nf2.dochd_iss_dt between '01-mai-2014' and '31-mai-2014'
               and nf.dochd_own_ent_id  = nf2.dochd_own_ent_id
               and nf2.dochd_st         = 91
               and nf2.dochd_doc_prc_tp = it2.docsv_doc_prc_tp
               and nf2.dochd_doc_prc_id = it2.docsv_doc_prc_id
               and it2.docsv_cd         not in ('AUR200','AUR001','01.02','01.01','01.03',
                                                '301.03','AUR002','02.02','02.01','02.03',
                                                '302.03','AUR094','07.01','07.04','03.01',
                                                '03.11','03.02','03.04','03.03','303.03',
                                                'AUR029','03.13','04.01','AUR004','04.03',
                                                '05.03','05.02')),0) as outros,
       sum(it.docsv_amt) total
  from dochd nf, docsv it, entdf cl
 where nf.dochd_iss_dt between '01-mai-2014' and '31-mai-2014'
   and nf.dochd_st     = 91
   and nf.dochd_doc_prc_tp = it.docsv_doc_prc_tp
   and nf.dochd_doc_prc_id = it.docsv_doc_prc_id
   and nf.dochd_own_ent_id = cl.entdf_id
group by nf.dochd_own_ent_id, cl.entdf_dsc

Share this post


Link to post
Share on other sites

Qual o motivo das colunas estarem em subselects ? Me parecem desnecessárias pois as tabelas são as mesmas do FROM, talvez um tratamento com CASE ou PIVOT seja melhor.

 

Quanto ao PHP não posso ajudar.

  • +1 1

Share this post


Link to post
Share on other sites

Qual o motivo das colunas estarem em subselects ? Me parecem desnecessárias pois as tabelas são as mesmas do FROM, talvez um tratamento com CASE ou PIVOT seja melhor.

 

Quanto ao PHP não posso ajudar.

os subselects eu utilizei pois são gerados campos distintos,mas vou reavaliar esta montagem. Agora a questão da variável ainda continuo com dúvida

Valewww

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

Ao usar o fórum, você concorda com nossos Terms of Use.