Josh Newlin Josh Newlin - 2 months ago 6
SQL Question

How can I bypass a null value on an INTO statement

I have this query I posted in another question, however this is a different issue. I am trying to get this value and store it into a variable. I know my query is pretty complex but it works, I just need to know how to have an exception to handle if it's null.

SELECT DISTINCT *
INTO MUSICIAN_NUM, NUM_ALBUMS
FROM (
SELECT MNO, COUNT(MNO)
FROM PERFORM
WHERE PERFORM.SNO IN (
SELECT DISTINCT S.ANO
FROM SONG S
WHERE S.SNO IN (
SELECT DISTINCT P.SNO
FROM PERFORM P
WHERE j IN P.MNO
GROUP BY P.SNO
)
)
AND PERFORM.MNO != SUB_MUSICIAN_NUM
AND PERFORM.MNO != TEMP_MUS_NUM
GROUP BY MNO
ORDER BY COUNT(MNO) DESC
)
WHERE ROWNUM = 1;


Some values will return with nothing, which it tries to store into
MUSICIAN_NUM
and
NUM_ALBUMS
.

I then try to print it here:

DBMS_OUTPUT.PUT_LINE(MUSICIAN_NUM || ' ' || MUSICIAN_NAME || ' ' || NUM_ALBUMS);


And I get this error:

DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 38

Answer

null is very different from no data. From the text of the question, it appears that you are trying to handle the case where the query returns no data. That would look something like this. I'm guessing that you want your two variables to be null if no data is found but you can put whatever you'd like in the exception handler.

BEGIN
  <<your query>>
EXCEPTION
  WHEN no_data_found
  THEN
    MUSICIAN_NUM := null;
    NUM_ALBUMS := null;
END;