CONDITION WITHIN WHERE CLAUSE

-1

Good afternoon!   I have a task to perform a select, but the parameters are passed as variables and I need to treat the input of one of these variables with the following logic: if & CT is not null, then I add in the condition of select the values that were suggested in this variable, if it is null, then I do not want to execute this condition on the select filter.    I'm working with Oracle PL / SQL.

select * from TABELA t 
WHERE TRUNC(T.DATASTAMP) BETWEEN '&DATA_INICIAL' AND '&DATA_FINAL'
AND ( CASE WHEN &CT IS NOT NULL THEN T.CENTRO_TRABALHO = &CT END );

This way it is giving error, both putting value in the variable and leaving it null.

    
asked by anonymous 20.09.2018 / 18:55

1 answer

1

You only need a or condition in your filter:

...
and (&CT is null or T.CENTRO_TRABALHO = &CT)

Another alternative, probably more performative than Or, would be to use the nvl in its variable, if it is null, returns the field itself that can be filtered:

...
and NVL(&CT, T.CENTRO_TRABALHO) = T.CENTRO_TRABALHO
    
20.09.2018 / 19:24