如果满足某些条件,我有一个查询必须从一个表中选择,如果条件不同,则必须从另一个表选择。
在我的情况下,如果我们在Database_A上,我需要从Table_A中选择;如果我们在Database _B上,则需要从Table_B中选择,但标准可能不同。
我想做这样的事情:
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)
我如何使用纯SQL而不是PL-SQL来实现它?我可以使用嵌套查询或WITH或类似的东西,但不能使用";编码";。我无法创建表或视图,只能在数据库中查询数据。
我尝试使用CASE或其他选项,但没有成功。
您可以使用条件条件中的数据库名称和UNION运算符从右表中进行选择。
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)
如果是从两个表中选择一个,那么这样的东西可能会有所帮助:
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
您可以将where子句放在cte定义(WITH子句(中,也可以放在主SQL中,或者放在两者中。
CASE表达式中的WHEN条件应该根据您的数据上下文来设置,这是从一个或另一个表中选择/筛选数据的地方如果两个数据库都无法访问这两个表,那么您应该有一个单独的脚本,每个数据库一个。不过,您可以检查哪个是活动数据库和/或是否有特定的表。
-- to get the db name ...
Select NAME "DB_NAME" From V$database
-- ... to check if the table exist ...
Select TABLE_NAME from ALL_TABLES where TABLE_NAME = 'TABLE_A'
-- ... or some additional info besides the table name
Select
u.USER_ID, u.USERNAME,
t.TABLE_NAME, t.OWNER "TABLE_OWNER"
From ALL_USERS u
Inner Join ALL_TABLES t ON(t.OWNER = u.USERNAME)
Where TABLE_NAME = 'TABLE_A'