Applying conditional statement in Oracle DB

Question

In the Oracle DB there are three tables, see below.

Table: 'People' people

Table: 'Dogs' dogs

Table: 'Cats' cats

Here is a Fiddle with data to test : http://sqlfiddle.com/#!4/d7cb4

Depending on the result of a query from the 'People' table, how can I proceed with a different query to request data further either from 'Dogs' or 'Cats'.

Simply saying I need to execute a corresponding query depending on the answer of the initial/main query. Here is what I tried, (which obviously does not work):

<!-- language: sql -->
WITH CONDITION_CHECK AS (
 SELECT ADORE
 FROM PEOPLE
 WHERE ADORE = 'dog' --here I will put my variable
)
SELECT
 CASE 
 WHEN CC.ADORE = 'dog' THEN (SELECT * FROM DOGS)
 WHEN CC.ADORE = 'cat' THEN (SELECT * FROM CATS)
 ELSE NULL
 END
FROM CONDITION_CHECK AS CC

What is the most common approach to set up a conditional statement in Oracle DB? And how can one execute it?

I have seen the IF-THEN-ELSE Statement, but I do not understand it much.

---

References:

• Performing a query on a result from another query?
• Using IF ELSE statement based on Count to execute different Insert statements
• IF Condition Perform Query, Else Perform Other Query

Answer 1

Depending on the result of a query from the 'People' table, how can I proceed with a different query to request data further either from 'Dogs' or 'Cats'.

You shouldn't.
The two table structure (shown) are identical, which suggests to me that they should be a single table with an additional "type" field to identify which is which (Ah; you already have that field).

The "Table-per-Thing" Model almost always breaks down at some point.
If you were running MySQL, you couldn't have any more than about sixty types of Pet, because that's the maximum number of tables you can have in any, single query!
Also, it makes things like this very complex.

Answer 2

As you were already told, data model is wrong.

---

Anyway, here's what you might try to do.

Sample data:

SQL> with
 2 people (id, name, adore) as
 3 (select 1, 'Stone' , 'dog' from dual union all
 4 select 3, 'Bridge', 'cat' from dual
 5 ),
 6 dogs (id, type, breed) as
 7 (select 1, 'dog', 'dobermann' from dual union all
 8 select 2, 'dog', 'border collie' from dual union all
 9 select 3, 'dog', 'labrador retriever' from dual
 10 ),
 11 cats (id, type, breed) as
 12 (select 1, 'cat', 'persian cat' from dual union all
 13 select 2, 'cat', 'bengal cat' from dual
 14 ),
 15 --

Query begins here; as both pets tables have the same description (meaning of columns and their datatypes), you can union them and then join such a CTE to people table.

You said you'd pass adore value as a parameter; that's wrong, I think; you'd select adore from table and pass adore? You already know it! That's why my query accepts id from the people table instead.

 16 all_pets as
 17 (select * from dogs
 18 union all
 19 select * from cats
 20 )
 21 select a.*
 22 from all_pets a join people p on p.adore = a.type
 23 where p.id = &par_id;
Enter value for par_id: 1
 ID TYP BREED
---------- --- ------------------
 1 dog dobermann
 2 dog border collie
 3 dog labrador retriever
SQL> /
Enter value for par_id: 3
 ID TYP BREED
---------- --- ------------------
 1 cat persian cat
 2 cat bengal cat
SQL>