CODEWITHSUNDEEP
sqloracle-databaseplsql
Josh Newlin
Josh Newlin

Reputation: 108

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

Upvotes: 0

Views: 313

Answers (1)

Justin Cave
Justin Cave

Reputation: 231791

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;

Upvotes: 3

Related Questions