CODEWITHSUNDEEP
javasqloracle-databaseplsqloracle-sqldeveloper
Michael Edwards
Michael Edwards

Reputation: 49

Oracle Like Operator in Where condition and decode

I'm trying to write a PL-SQL query to return records from multiple table and using decode to allow null value comparison,

PROCEDURE GetResults(FromDate DATE,pToDate DATE, DeptName VARCHAR2 DEFAULT NULL,p_ref  OUT SYS_REFCURSOR ) IS
BEGIN
OPEN p_ref FOR
SELECT e.First_Name,e.LastName,e.Department
FROM
Employee e, Department d
Where  e.JoiningDate >=FromDate
      AND e.JoiningDate <=ToDate
     AND  NVL(e.Department,'~') = decode(DeptName,null,NVL(e.Department,'~'),DeptName
End GetResults

The result is returning only where DeptName is exactly matching to the database values, but I want a query to return something e.Department like '%DeptName%'

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

No need for anything other than boolean logic:

WHERE e.JoiningDate >= FromDate AND
      e.JoiningDate <= ToDate AND
      (DeptName IS NULL OR e.Department = DeptName)

If you want a NULL parameter to only match NULL values, then use:

WHERE e.JoiningDate >= FromDate AND
      e.JoiningDate <= ToDate AND
      (DeptName IS NULL AND e.Department IS NULL OR 
       e.Department = DeptName
      )

I would also recommend that you give your parameters names that are more obviously parameters. I often prefix with in_ or out_:

PROCEDURE GetResults (
    in_FromDate DATE,
    in_ToDate DATE,
    in_DeptName VARCHAR2 DEFAULT NULL,
    out_p_ref  OUT SYS_REFCURSOR
) IS

Upvotes: 1

Related Questions