Condition within the WHERE clause

Asked

Viewed 849 times

-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 perform this condition in the select filter. I am working with PL/SQL Oracle.

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 is giving error, both putting value in the variable and leaving it null.

oracle select pl-sql oracle11g where

  • can’t be done like this, the case when should return a value, based on several cases, and compare with something at the end, for example: where campo = (case campo2=1 then 10 else 20 end), will always compare "field" to a value that depends on "field2"

2 answers

2

You only need one condition or in your filter:

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

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

...
and NVL(&CT, T.CENTRO_TRABALHO) = T.CENTRO_TRABALHO

0

You can filter as follows:

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

Browser other questions tagged oracle select pl-sql oracle11g where

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