I have two queries at the moment, returning the same data, I want the second query to run if the first returns no results, here's the basic idea of my PHP right now:
Run Query1
If Query1 returns a result
Enter data into array
Else
Run Query2
Enter data into array
What I'm wondering, is can I do that exclusively using Oracle SQL? So it'd just run a single query to the database, do a quick check of the first statement, and if no results are found run the second one?
Thanks
QUERY1
UNION ALL
QUERY2
WHERE NOT EXISTS (QUERY1)
e.g:
SELECT id
FROM products
UNION ALL
SELECT id
FROM old_products
WHERE NOT EXISTS (SELECT id FROM products)
Query:
SELECT *
FROM Table
WHERE ID = 7
AND Flag = 1
UNION ALL
SELECT *
FROM Table
WHERE ID = 7
AND NOT EXISTS (select * from Table where ID = 7 and Flag = 1)
To identifiy unique rows you can try this as well:
WITH qry AS
(
SELECT a.* ,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY Flag DESC ) rn
FROM Table a
WHERE ID = 7 -- THIS CAN BE PARAMETERIZED
)
SELECT *
FROM qry
WHERE rn = 1
You can put the 2nd query and the associated logic into the when "no_data_found" exception. For example:
begin
Query 1
do stuff
exception when no_data_found then
query 2
do stuff
end;
Hope this helps.
PLSQL (Oracle's flavor of sql) can do IF-THEN-ELSE statements -- here's a sample that should do what you need...
can the second query run even if the first returns results?
if so, then a UNION would do it.
if not, then i think UNION is still the right answer, just write the second query WHERE clasue to restrict when the first query records exist.
SELECTs? Do they return compatible records?