How, in Oracle, to SELECT FROM a table or another depending on certain criteria?-CodePudding

I have a query that must choose from one table if certain criteria is meet or from another if the criteria is different.

In my case i need to select from Table_A if we are on Database_A or from Table_B if we are on Database_B, but the criteria could be a different one.

I want to do something like this:

SELECT
    COLUMN_1, COLUMN_2, ..., COLUMN_N
FROM
    (if database is Database_A then Table_A
     but if database is Database_B then from Table B
     else... it could be DUAL if any other situation, it will throw an error, but i can manage it)
WHERE
    (my where criteria)

How can i do it with pure SQL not PL-SQL? I can use nested queries or WITH or similar stuff, but not "coding". I cannot create tables or views, i can only query the database for data.

I tried using CASE or other options, with no luck.

CodePudding user response：

You may use the database name in the condition criteria and UNION operator to select from the right table.

    SELECT COLUMN_1, COLUMN_2, ..., COLUMN_N
    FROM
    (
     SELECT COLUMN_1, COLUMN_2, ..., COLUMN_N
     FROM TableA
     WHERE ora_database_name = 'DatabaseA'
     UNION ALL
     SELECT COLUMN_1, COLUMN_2, ..., COLUMN_N
     FROM TableB
     WHERE ora_database_name = 'DatabaseB'
     UNION ALL
     SELECT 'ERROR', null, ..., null
     FROM DUAL
     WHERE ora_database_name NOT IN ('DatabaseA', 'DatabaseB')
     )
     WHERE
     (my where criteria)

CodePudding user response：

If it is the case of selecting from one of two tables then something like this could help:

WITH
    tab_a AS
        (   Select COL_1, COL_2, COL_3 From TABLE_A  ),   -- It is possible to do the filter here or in the main SQL or both
    tab_b AS
        (   Select COL_1, COL_2, COL_3 From TABLE_B  )    -- It is possible to do the filter here or in the main SQL or both

Select DISTINCT
    CASE WHEN 1 = 1 THEN a.COL_1 ELSE b.COL_1 END "COL_1",
    CASE WHEN 1 = 1 THEN a.COL_2 ELSE b.COL_2 END "COL_2",
    CASE WHEN 1 = 1 THEN a.COL_3 ELSE b.COL_3 END "COL_3"
From
    tab_a a
Inner Join
    tab_b b ON(1 = 1)
Where
    CASE WHEN 1 = 1 THEN b.COL_1 ELSE a.COL_2 END = 'XXX'   -- filter could be set on the same columns or on different ones of the same type

You can put the where clause either in the cte definition (WITH clause) or in the main SQL or in both.

WHEN condition within CASE expresions should be put acording to your data context and that is the place that will select/filter the data from one or another table.
INNER JOIN ON condition may stay as is (1=1) for it creates cross link that will be solved by the DISTINCT keyword.