Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL
PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL [message #423023] Mon, 21 September 2009 19:50 Go to next message
anonymous2009
Messages: 23
Registered: June 2009
Junior Member
Hello,
I have a table in which a particular column (Eg: col Y) may
or may not have value.

I need to implement a stored procedure in which I need to
fetch rows from this table with :
column X = <Input value of I_X>
AND column Y= <Input value of I_Y>

Table:
Create table Dummy
(A NUMBER, --PK column
X NUMBER,
Y VARCHAR2(50)
);

Rows in the above table are:
1000 1 APPLE
1001 1


Stored Procedure:
Create OR Replace FUNCTION GetDummy
(I_X NUMBER, --mandatory INPUT
I_Y VARCHAR2(50)
)
RETURN SYS_REFCURSOR
IS
ret_output SYS_REFCURSOR;
BEGIN
-- Code to check for mandatory input
<SQL.....Need help with this>
RETURN ret_output;
END;

Could somebody help me to put together the SQL, without using NVL on column in WHERE clause?

Meaning,
Scenario 1:
begin
-- Call the function
:result := GetDummy(I_X => 1,I_Y => 'APPLE');
end;

Following row must be returned in output cursor:
1001 1 APPLE


Scenario 2:
begin
-- Call the function
:result := GetDummy(I_X => 1, I_Y => NULL);
end;

Both rows must be returned in output cursor:
1000 1
1001 1 APPLE

Basically what I am trying to achieve here is,
Scenario 1: if value is given for I_Y, then row with X=I_X and Y=I_Y alone must be returned.
Scenario 2: If I_Y is left NULL, then all rows with X=I_X must be returned.

How do I do it without using NVL on column in SQL WHERE clause?
Thanks.
Re: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL [message #423024 is a reply to message #423023] Mon, 21 September 2009 19:55 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>How do I do it without using NVL on column in SQL WHERE clause?
Never a valid business requirement; but classic homework assignment.
Re: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL [message #423050 is a reply to message #423023] Mon, 21 September 2009 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste what you already tried.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL [message #423055 is a reply to message #423023] Mon, 21 September 2009 23:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, like BlankSwan I too am interested in what the requirement is that says you cannot use NVL.

There are alternatives to NVL but many of them are more work not less. For exmaple:

UNION ALL between multiple sql statements

DECODE or CASE though these are just variations of NVL when used this way.

You could even do something like a fancy OUTER-JOIN from a made up list but that work.

Also, as was suggested by Michele, use the editing tools available to you above where you type in your message text. Play with them till you figure out what they do. One of them {..} is for formatting code.

Good luck, KEvin
Re: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL [message #423061 is a reply to message #423023] Tue, 22 September 2009 00:02 Go to previous messageGo to next message
anonymous2009
Messages: 23
Registered: June 2009
Junior Member
Hello,
The reason I did not want to use NVL is to avoid creating function index.
I have figured out a solution for this.


Create OR Replace FUNCTION GetDummy
(I_X NUMBER, --mandatory INPUT 
I_Y VARCHAR2(50) 
)
RETURN SYS_REFCURSOR
IS
ret_output SYS_REFCURSOR;
BEGIN
 -- Code to check for mandatory input

 OPEN ret_output FOR
  SELECT *
    FROM Dummy
   WHERE Dummy.X  = I_X
     AND (I_Y IS NULL
          OR I_Y = Dummy.Y
         );
RETURN ret_output;
END;


Please let me know other efficient alternatives for this.
Thanks.
Re: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL [message #423066 is a reply to message #423061] Tue, 22 September 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can create an index on (X,Y) there is no need of a FBI (with or without NVL, I prefer this later one).

Regards
Michel

[Updated on: Tue, 22 September 2009 00:39]

Report message to a moderator

Re: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL [message #423069 is a reply to message #423061] Tue, 22 September 2009 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you would compare Dummy.Y with nvl(I_Y, Dummy.Y), you would not need an index on the nvl-part, but on Dummy.Y.
Re: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL [message #423092 is a reply to message #423069] Tue, 22 September 2009 01:41 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Since you would compare Dummy.Y with nvl(I_Y, Dummy.Y), you would not need an index on the nvl-part, but on Dummy.Y.



comparing Dummy.Y with nvl(I_Y, Dummy.Y) is different with what OP has suggested
Quote:
AND (I_Y IS NULL
OR I_Y = Dummy.Y
);


as in the first case wrong output will be returned for Null values of Dummy.Y

[Updated on: Tue, 22 September 2009 01:42]

Report message to a moderator

Re: PLSQL - Fetch all matching rows when input is NULL and specific row when input is NOT NULL [message #423195 is a reply to message #423023] Tue, 22 September 2009 11:02 Go to previous message
anonymous2009
Messages: 23
Registered: June 2009
Junior Member
The business case is:
i)If value is given for I_X and no value for I_Y, return all rows from Dummay with matching dummy.X = I_X.
ii) If value for I_X and I_Y is given, then return rows from Dummy table that match these input values.

Previous Topic: trigger problem
Next Topic: How to convert this particular SQL query to PL/SQL query
Goto Forum:
  


Current Time: Sat Sep 24 18:02:59 CDT 2016

Total time taken to generate the page: 0.15413 seconds