1

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:

2 Answers 2

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.

1

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.