In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!
--Return this if there are rows
SELECT * FROM table WHERE firstCriteria = "firstValue"
-- Else Return this if there are rows
SELECT * FROM table WHERE secondCriteria = "secondValue"
-- Else Return this if there are rows
SELECT * FROM table WHERE thirdCriteria = "thirdValue"
-- etc.
DECLARE
v_check INTEGER;
v_cur SYS_REFCURSOR;
BEGIN
SELECT COUNT(*)
INTO v_check
FROM yourtable
WHERE firstcriteria = 'firstValue' AND ROWNUM = 1;
--Return this if there are rows
IF v_check = 1
THEN
OPEN v_cur FOR
SELECT *
FROM yourtable
WHERE firstcriteria = 'firstValue';
ELSE
SELECT COUNT(*)
INTO v_check
FROM yourtable
WHERE secondcriteria = 'secondValue' AND ROWNUM = 1;
--Return this if there are rows
IF v_check = 1
THEN
-- Else Return this if there are rows
OPEN v_cur FOR
SELECT *
FROM yourtable
WHERE secondcriteria = 'secondValue';
ELSE
SELECT COUNT(*)
INTO v_check
FROM yourtable
WHERE thirdcriteria = 'thirdValue' AND ROWNUM = 1;
--Return this if there are rows
IF v_check = 1
THEN
-- Else Return this if there are rows
OPEN v_cur FOR
SELECT *
FROM yourtable
WHERE thirdcriteria = 'thirdValue';
END IF;
END IF;
END IF;
END;
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
See if this is what you are after.
Open in new window