CODEWITHSUNDEEP
sqloracle-database
xn dx
xn dx

Reputation: 793

If data exists, else... using Oracle SQL?

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

Upvotes: 0

Views: 7321

Answers (4)

Chandu
Chandu

Reputation: 82933

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

Upvotes: 3

Chains
Chains

Reputation: 13167

PLSQL (Oracle's flavor of sql) can do IF-THEN-ELSE statements -- here's a sample that should do what you need...

http://www.techonthenet.com/oracle/loops/if_then.php

Upvotes: 2

Matt M
Matt M

Reputation: 3779

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.

Upvotes: 2

Randy
Randy

Reputation: 16673

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.

Upvotes: 1

Related Questions