CODEWITHSUNDEEP
sqloracle-databasenullcase
Sana
Sana

Reputation: 445

Oracle case for null

I was searching for this one for a pretty long time and went through several similar questions on this web but couldn't get my query solved. i have a stored procedure in which i have a parameter SP_BRANCH_ID, i want a case that if it is null then fkBranchId = null in my table gets retrieved or else the the ones specified in Sp_branch_id.

PROCEDURE USP_GET_TAX_RATES_BY_ID(SP_CURSOR OUT T_CURSOR,SP_BRANCH_ID IN NUMBER)
IS 
BEGIN 
   OPEN SP_CURSOR FOR
      SELECT "TaxRates".* from "TaxRates" where "IsDeleted" = 'N' AND "fkBranchId" =(
         case when  SP_BRANCH_ID is null then null else (SP_BRANCH_ID) end );
END USP_GET_TAX_RATES_BY_ID;

Upvotes: 0

Views: 1879

Answers (2)

psaraj12
psaraj12

Reputation: 5072

You can replace the null check with a string 

SELECT "TaxRates".* from "TaxRates"
   WHERE "IsDeleted" = 'N'
   and NVL("fkBranchId" ,'null')=NVL(SP_BRANCH_ID,'null');

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

When the SP_BRANCH_ID is null , you are assigning "fkBranchId" = NULL, which is wrong usage in Oracle.

Use IS NULL for null checks.

PROCEDURE USP_GET_TAX_RATES_BY_ID(SP_CURSOR OUT T_CURSOR,SP_BRANCH_ID IN NUMBER)
IS 
BEGIN 
   OPEN SP_CURSOR FOR
      SELECT "TaxRates".* from "TaxRates"
       WHERE "IsDeleted" = 'N'
       AND ( "fkBranchId" = SP_BRANCH_ID OR (SP_BRANCH_ID IS NULL AND "fkBranchId" IS NULL))
END USP_GET_TAX_RATES_BY_ID;

Upvotes: 2

Related Questions