I am trying to write an Oracle query that accepts multiple columns of a table and returns rows for each column with a flag whether the input column exists or not?
Table DDL -
CREATE TABLE orders
(
id NUMBER(10),
order_mode VARCHAR2(8),
cust_id NUMBER(6),
status NUMBER(2)
)
To find out which column is present in the table, I execute below query. But it fails with the error message "SQL Error [936][42000]: ORA -00936: missing expression" fail.
SELECT column_name,
exists (
SELECT 1
FROM all_tab_columns
WHERE table_name = 'ORDERS'
AND column_name IN ('ORDER_MODE', 'CUST_ID', 'ABC')
) AS exists_one_zero
FROM dual;
The below query also fails with "SQL Error [904] [42000]: ORA-00904: COLUMN_NAME: invalid identifier".
SELECT column_name,
CASE WHEN exists (
SELECT 1
FROM all_tab_columns
WHERE table_name = 'ORDERS'
AND column_name IN ('ORDER_MODE', 'CUST_ID', 'ABC')
) THEN 'Yes' ELSE 'No' END AS exists_yes_no
FROM dual;
May I know what is the problem in above queries ?
Your first query fails because Oracle does not allows a predicate such as exists in the select clause like that; some database would turn it to a boolean value, but in Oracle you need to wrap the predicate in a case expression (it will work in 23c though).
In the second query, the problem is that column_name is not defined in the outer select, which is from dual (only the inner select sees that column).
Overall, I would recommend changing the logic to generate a fixed list of columns as rows, then check their presence in all_tab_columns with exists:
select c.column_name,
case when exists (
select 1
from all_tab_columns t
where t.table_name = 'ORDERS' and t.column_name = c.column_name
) then 'YES' else 'NO' end exists_yes_no
from (
select 'ORDER_MODE' as column_name from dual
union all select 'CUST_ID' from dual
union all select 'ABC' from dual
) c
You could took this one step forward and make the table name a variable as well, so you could perform the same check across tables:
select c.column_name,
case when exists (
select 1
from all_tab_columns t
where t.table_name = c.table_name and t.column_name = c.column_name
) then 'YES' else 'NO' end exists_yes_no
from (
select 'ORDERS' table_name, 'ORDER_MODE' as column_name from dual
union all select 'CUSTOMERS', 'CUST_ID' from dual
) c
Side note: I would recommend adding column OWNER to the WHERE clause of the ALL_TAB_COLUMNS lookup, since different tables may have the same name in different schemas. Alternatively consider querying USER_TAB_COLUMNS, which lets you access your own schema only.