NVL Command

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 15 May 2006 12:25 pm

Good morning guys, I am here again bothering you.
But I go there ....

I'vê got a problem here in my SQL, I have one here in my Select parameter which can return null and null case, have to search in another table of the possible values for this parameter.

I tried to do this NVL (PARAMETER, ' SELECT * FROM table ') I couldn't, I wanted help from vocês to answer whether it would be possible to do this in a different way or I'm using NVL command wrong.

Expect an answer!!!

And many thanks in advance ...: lol:
jctq
Location: São Gonçalo

Att,
Julio Quintão

Poston Fri, 02 Jun 2006 7:57 am

Guy, a suggestion would you create a function in the Bank and call her when the parameter was null.
Eg: SELECT * FROM ra_salesreps WHERE name IN nvl (null, pk_calcula_cota. get_vendedor (1000)) hope this helps.
Valdo
Location: Medianeira/pr

Poston Fri, 02 Jun 2006 10:12 am

Even easier: can use CASE WHEN. Dai it accepts putting a sub-select inside.
See example:
Code: 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>
:-
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests