2023年5月25日 21:36:11go评论100阅读模式

英文:

Oracle - Check the presence of columns (yes/no) in a table

问题

I'm sorry, but it seems like you're asking for a code translation, and you've requested not to provide translations for code. If you have any non-code related questions or need assistance with something else, please feel free to ask, and I'll be happy to help.

英文:

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 = &#39;ORDERS&#39;
          AND column_name IN (&#39;ORDER_MODE&#39;, &#39;CUST_ID&#39;, &#39;ABC&#39;)
       ) 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 = &#39;ORDERS&#39;
          AND column_name IN (&#39;ORDER_MODE&#39;, &#39;CUST_ID&#39;, &#39;ABC&#39;)
       ) THEN &#39;Yes&#39; ELSE &#39;No&#39; END AS exists_yes_no
FROM dual;

May I know what is the problem in above queries ?

答案1

得分: 2

Your first query fails because Oracle does not allow a predicate such as exists in the select clause like that; some databases would turn it into 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 take this one step further 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 the 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 schema only.

英文:

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 = &#39;ORDERS&#39; and t.column_name = c.column_name
    ) then &#39;YES&#39; else &#39;NO&#39; end exists_yes_no
from (
    select &#39;ORDER_MODE&#39; as column_name from dual
    union all select &#39;CUST_ID&#39;         from dual
    union all select &#39;ABC&#39;             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 &#39;YES&#39; else &#39;NO&#39; end exists_yes_no
from (
    select &#39;ORDERS&#39; table_name, &#39;ORDER_MODE&#39; as column_name from dual
    union all select &#39;CUSTOMERS&#39;, &#39;CUST_ID&#39; 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.

答案2

得分: 1

你不需要使用 EXISTS。而是可以左连接两个查询 - 一个定义要与表进行列名测试的列名列表，另一个从 all_tab_columns 中获取表的列名。使用 Case 表达式来检查存在性。

Select  
    l.COLUMN_NAME, 
    CASE WHEN c.COLUMN_NAME Is Null THEN 'NO' ELSE 'YES' END "EXISTS"  
From      
    (   Select 'ORDER_MODE' "COLUMN_NAME" From Dual Union All
        Select 'CUST_ID'     From Dual Union All
        Select 'SOME_COL'    From Dual  ) l
Left Join    
    ( Select COLUMN_NAME 
      From all_tab_columns 
      Where TABLE_NAME = 'ORDERS' And
            OWNER = 'TAB_OWNER_NAME') c ON(c.COLUMN_NAME = l.COLUMN_NAME)

结果：
COLUMN_NAME EXISTS

ORDER_MODE YES
CUST_ID YES
SOME_COL NO

英文:

You don't need to use EXISTS. Instead you could Left Join two queries - the one that defines your list of column names to be tested against a table and the other fetching column names of your table from all_tab_columns. Check the existance using Case expression

Select  
    l.COLUMN_NAME, 
    CASE WHEN c.COLUMN_NAME Is Null THEN &#39;NO&#39; ELSE &#39;YES&#39; END &quot;EXISTS&quot;  
From      
    (   Select &#39;ORDER_MODE&#39; &quot;COLUMN_NAME&quot; From Dual Union All
        Select &#39;CUST_ID&#39;     From Dual Union All
        Select &#39;SOME_COL&#39;    From Dual  ) l
Left Join    
    ( Select COLUMN_NAME 
      From all_tab_columns 
      Where TABLE_NAME = &#39;ORDERS&#39; And
            OWNER = &#39;TAB_OWNER_NAME&#39;) c ON(c.COLUMN_NAME = l.COLUMN_NAME)
--  
--  R e s u l t :       
--  COLUMN_NAME EXISTS
--  ----------- ------
--  ORDER_MODE  YES
--  CUST_ID     YES
--  SOME_COL    NO

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

检查表中列的存在（是/否）。

问题

答案1

答案2

SQL产品变种表设计

在SQL中对连续数值的序数编号

使用Informix的DB2驱动程序

将两个范围插入到SQL命令文本中，Excel VBA

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。