NVL command

[jctq]

Good morning people, I'm here again for you.
But I go there ....

I have a problem here in a SQL of mine, I have a parameter here in my select that can come back null and if you go back null, I have to Search on another table the possible values ??for this parameter.

I tried to make ISS

Select all

 NVL (PARAMETRO,'SELECT * FROM TABELA')

I could not, I wanted help from you to answer if it would be possible to do this otherwise or I'm using the wrong NVL command.

I hope an answer !!!

And thank you from now ....
: LOL:

[Valdo]

Dude, a suggestion would be to create a function on the bench and call it when the parameter was null.
Ex.:

Select all

SELECT * FROM ra_salesreps 
WHERE name IN nvl(null,pk_calcula_cota.get_vendedor(1000))

I hope this helps.

[dr_gori]

Easier:
You can use Case When. Dai it accepts to put a sub-select inside.
See example:

Select all

SQL> SELECT  
  2    A.ENAME 
  3  , A.COMM 
  4  , CASE WHEN A.COMM IS NULL THEN (SELECT B.JOB  
  5                                   FROM EMP B  
  6                                   WHERE A.EMPNO=B.EMPNO) 
  7    END CONSULTA  
  8  FROM EMP A 
  9  / 
 
ENAME            COMM CONSULTA 
---------- ---------- --------- 
SMITH                 CLERK 
ALLEN             300 
WARD              500 
JONES                 MANAGER 
MARTIN           1400 
BLAKE                 MANAGER 
CLARK                 MANAGER 
SCOTT                 ANALYST 
KING                  PRESIDENT 
TURNER              0 
ADAMS                 CLERK 
JAMES                 CLERK 
FORD                  ANALYST 
MILLER                CLERK 
 
14 rows selected. 
 
SQL>  

: -O