Reuse of Clause AND (ORACLE)

Asked

Viewed 40 times

1

Eai Galera.

I need to run a select where it gets very repetitive my conditions being various joins in the same table, Follows:

select
elfo.el_field_option_id "Centro de Custo ID", 
elfo.label "Centro de Custo", 
elfop_1.el_field_option_id "Empresa ID",
elfop_1.label "Empresa",
elfop_2.el_field_option_id "Unidade Gerencial ID",
elfop_2.label "Unidade Gerencial",
elfop_3.el_field_option_id "Segmento ID",
elfop_3.label "Segmento",
elfop_4.el_field_option_id "Sigla ID",
elfop_4.label "Sigla"
from el_field_option elfo
LEFT join el_field_option elfop_1 
    on elfop_1.el_field_option_parent_id = elfo.el_field_option_id
LEFT join el_field_option elfop_2 
    on elfop_2.el_field_option_parent_id = elfop_1.el_field_option_id
LEFT join el_field_option elfop_3 
    on elfop_3.el_field_option_parent_id = elfop_2.el_field_option_id
LEFT join el_field_option elfop_4 
    on elfop_4.el_field_option_parent_id = elfop_3.el_field_option_id
where elfo.el_field_id = 1620 
        AND ELFO.STATUS = 1 
        AND elfop_1.STATUS = 1 --DUVIDA AQUI
        AND elfop_2.STATUS = 1 --DUVIDA AQUI
        AND elfop_3.STATUS = 1 --DUVIDA AQUI
        AND elfop_4.STATUS = 1 --DUVIDA AQUI
        AND ELFO.STATUS = 1 --DUVIDA AQUI
        AND elfop_1.EL_FIELD_ID =1620
        AND elfop_2.EL_FIELD_ID = 1620 --DUVIDA AQUI
        AND elfop_3.EL_FIELD_ID = 1620 --DUVIDA AQUI
        AND elfop_4.EL_FIELD_ID = 1620; --DUVIDA AQUI

It is possible to pass only one condition to all status columns where "status" is 1 and EL_FIELD_ID = 1620?

query oracle where

  • Mathematically you can multiply all fields and test if the result is 1, which will only occur if they all have the value 1.

1 answer

1


You can make the direct call JOIN of status and of el_field_id:

...
from el_field_option elfo
LEFT join el_field_option elfop_1 
    on elfop_1.el_field_option_parent_id = elfo.el_field_option_id 
   and elfop_1.status = elfo.status
   and elfop_1.EL_FIELD_ID = elfo.EL_FIELD_ID
LEFT join el_field_option elfop_2 
    on elfop_2.el_field_option_parent_id = elfop_1.el_field_option_id 
   and elfop_2.status = elfop_1.status
   and elfop_2.EL_FIELD_ID = elfop_1.EL_FIELD_ID
LEFT join el_field_option elfop_3 
    on elfop_3.el_field_option_parent_id = elfop_2.el_field_option_id 
   and elfop_3.status = elfop_2.status
   and elfop_3.EL_FIELD_ID = elfop_2.EL_FIELD_ID
LEFT join el_field_option elfop_4 
    on elfop_4.el_field_option_parent_id = elfop_3.el_field_option_id 
   and elfop_4.status = elfop_3.status
   and elfop_4.EL_FIELD_ID = elfop_3.EL_FIELD_ID
 where elfo.el_field_id = 1620 
   AND ELFO.STATUS = 1

Browser other questions tagged query oracle where

You are not signed in. Login or sign up in order to post.