CODEWITHSUNDEEP
oracle-databaseoracle12c
sam
sam

Reputation: 2596

How to return Null when a select statement returns no data found

I have these three select statements and I am trying to return null to the variable vCLASS1_ID or vCLASS2_ID or vCLASS3_ID when no data is found. 

  SELECT MAX (CLASS1_ID), MAX(CLASS1_DESC)
    INTO vCLASS1_ID, vCLASS1_DESC
    FROM PRODUCT_CLASSIFICATION
   WHERE CLASS1_DESC = P_CLASS1_DESC
GROUP BY CLASS1_ID, CLASS1_DESC;

   SELECT MAX (CLASS2_ID),MAX(CLASS2_DESC)
     INTO vCLASS2_ID,vCLASS2_DESC
     FROM PRODUCT_CLASSIFICATION
    WHERE CLASS2_DESC = P_CLASS2_DESC
GROUP BY CLASS2_ID, CLASS2_DESC;

   SELECT MAX (CLASS3_ID),MAX(CLASS3_DESC)
     INTO vCLASS3_ID,vCLASS3_DESC
     FROM PRODUCT_CLASSIFICATION
    WHERE CLASS3_DESC = P_CLASS3_DESC
GROUP BY CLASS3_ID, CLASS3_DESC;

I tried to solve that problem by below ways, but I fail

Any other simple ways to do it ?

Does the declaring 3 courser is the better solution ?

Upvotes: 4

Views: 11709

Answers (2)

APC
APC

Reputation: 146179

"using EXCEPTION when no_data_found then to variable will not solve my problem since the function will stop running"

Use anonymous blocks to define local exception sections. This approach allows you to handle each occurrence separately without causing the larger program unit to abend. 

begin
      SELECT CLASS1_ID 
      INTO vCLASS1_ID
        FROM PRODUCT_CLASSIFICATION
       WHERE CLASS1_DESC = 'EXAMPLE';
exception
    when no_data_found then
        vCLASS1_ID := null;
end;        

begin
      SELECT CLASS2_ID 
      INTO vCLASS2_ID
        FROM PRODUCT_CLASSIFICATION
       WHERE CLASS2_DESC = 'EXAMPLE';
exception
    when no_data_found then
        vCLASS2_ID := null;
end;        

begin
      SELECT CLASS3_ID 
      INTO vCLASS3_ID
        FROM PRODUCT_CLASSIFICATION
       WHERE CLASS3_DESC = 'EXAMPLE';
exception
    when no_data_found then
        vCLASS3_ID := null;
end;

The above solution is a general approach to handling exceptions. However, the solution suggested by @MaheswaranRavisankar to this particular problem (set variable to null when no data found) requires less typing:

      SELECT max(CLASS3_ID) 
      INTO vCLASS3_ID
        FROM PRODUCT_CLASSIFICATION
       WHERE CLASS3_DESC = 'EXAMPLE';

The potential catch with this approach is that it would return no rows if we go with the OP's original formulation:

 SELECT max(CLASS3_ID) 
      INTO vCLASS3_ID
        FROM PRODUCT_CLASSIFICATION
       WHERE CLASS3_DESC = 'EXAMPLE'
 GROUP BY CLASS3_ID;

It is not clear why the OP is using GROUP BY in their original queries. If the table has potential for duplicate CLASSn_IDs then the correct approach is either:

  • to hurl and handle TOO_MANY_ROWS exception if there are duplicates
  • to apply the business rules which establish the current valid Class ID

However, if neither of those will satisfy the remaining open approach is 

begin
      SELECT CLASS3_ID 
      INTO vCLASS3_ID
        FROM PRODUCT_CLASSIFICATION
       WHERE CLASS3_DESC = 'EXAMPLE'
      GROUP BY CLASS3_ID;
exception
    when no_data_found then
        vCLASS3_ID := null;
end;

Upvotes: 7

Ming
Ming

Reputation: 211

I think it can be done by use outer join 

WITH TEMP AS (SELECT 'EXAMPLE' AS VAL FROM DUAL)
SELECT T1.CLASS1_ID 
  --INTO vCLASS1_ID
    FROM PRODUCT_CLASSIFICATION T1,
         TEMP T2
    WHERE T1.CLASS1_DESC(+) = T2.VAL
GROUP BY T1.CLASS1_ID

Upvotes: 2

Related Questions