Consulta utilizando variáveis dentro do Between

[asacap1000 0]

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

[Motta 645]

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.

[asacap1000 0]

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