Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
--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.
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));
insert into tab1 values('a','1');
insert into tab1 values('b','1');
insert into tab1 values('c','1');
commit;
select col1,col2 from
(
select col1, col2, criteria, min(criteria) over() min_criteria from
(
select 1 criteria, t1.* from tab1 t1 where col1='z'
union
select 2 criteria, t1.* from tab1 t1 where col1='y'
union
select 3 criteria, t1.* from tab1 t1 where col1='c'
)
)
where criteria=min_criteria
/
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;
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.