CODEWITHSUNDEEP
sqloracle-databasestored-procedures
gifpif
gifpif

Reputation: 4917

Check null condition in where clause

In my PROCEDURE if IN_CLIENT_ID is null then its return the row where GLOBAL_ID is null and if IN_CLIENT_ID have some value then its return the row that GLOBAL_ID=IN_CLIENT_ID.
When i use GLOBAL_ID=IN_CLIENT_ID result is ok if IN_CLIENT_ID have some value.But not for if IN_CLIENT_ID is null.

Then i check IN_CLIENT_ID is null out side of query and its working. But i don't want to write same query two times.

CREATE OR REPLACE PROCEDURE PROCEDURE123(
  IN_CLIENT_ID IN VARCHAR2,
  EXP_RECORDSET OUT SYS_REFCURSOR
) AS
BEGIN 
    IF IN_CLIENT_ID IS NULL THEN
        OPEN EXP_RECORDSET FOR  
        SELECT client.id  FROM GLOBAL_CLIENT client 
              WHERE  AND client.GLOBAL_ID IS NULL
          ORDER BY client.id;
    ELSE
        OPEN EXP_RECORDSET FOR  
        SELECT client.id  FROM GLOBAL_CLIENT client 
              WHERE  AND client.GLOBAL_ID=IN_CLIENT_ID
          ORDER BY client.id;
    END IF;
END;

Is there any way apply if condition in WHERE clause.

Upvotes: 0

Views: 238

Answers (2)

toonice
toonice

Reputation: 2246

Please try the following...

CREATE OR REPLACE PROCEDURE PROCEDURE123(
    IN_CLIENT_ID IN VARCHAR2,
    EXP_RECORDSET OUT SYS_REFCURSOR
) AS
BEGIN 
    OPEN EXP_RECORDSET FOR  
    SELECT client.id
    FROM GLOBAL_CLIENT client
    WHERE DECODE( client.GLOBAL_ID,
                  client.IN_CLIENT_ID,
                  1,
                  0 ) = 1
    ORDER BY client.id;
END;

The above is as per https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:4410543900346652511.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Upvotes: 2

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

You can do this by USING AND OR conjunctions in where clause

OPEN EXP_RECORDSET FOR  
    SELECT client.id  
    FROM GLOBAL_CLIENT client 
    WHERE  ( IN_CLIENT_ID IS NULL AND client.GLOBAL_ID IS NULL) OR 
           ( IN_CLIENT_ID IS NOT NULL AND client.GLOBAL_ID=IN_CLIENT_ID)
    ORDER BY client.id;

Upvotes: 1

Related Questions