CONDITION WITHIN WHERE CLAUSE

Question

CONDITION WITHIN WHERE CLAUSE

Navigation

#1 by (1 votes)

-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.

oracle oracle11g select pl-sql where

asked by anonymous 20.09.2018 / 18:55

1 answer

Enter the name of the variables in the first row of the dataframe Method Search for bank records on the same page as the PHP form

score 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