如何在Oracle中找到特定值的精确列名称

Question

我有一个报告要求，该报告应包含匹配以下标准的行（销售）中具有类似列名称的以下条件的行，例如

CUST_ID, DEPT,CODE1_ID,CODE2_ID,CODE3_ID,CODE1_ID_STOP_DT,CODE2_ID_STOP_DT,CODE3_ID_STOP_DT

示例数据：

CUST_ID, DEPT,CODE1_ID,CODE2_ID,CODE3_ID,CODE1_ID_STOP_DT,CODE2_ID_STOP_DT,CODE3_ID_STOP_DT.
123,    11,  10,      20,      100,     NULL,            31-OCT-2022,     31-DEC-2022
234,    21,  NULL,    100,     30,      NULL,            NULL,            31-DEC-2022
345,    11,  100,     30,      NULL,    NULL,            NULL,            31-JUL-2022
456,    31,  10,      60,      100,     01-JAN-2023,     NULL,            31-OCT-2022

fetch行的标准：代码为100，stop_dt不是31-dec-dec-2022或无效。 我能够用以下代码100获取行。

select * from SALES where '100' in (nvl(CODE1_id,0), nvl(CODE2_id,0),nvl(CODE3_id,0);

但是我无法编写SQL来获取代码的行*_id_stop_dt列 该值为null或不是31-DEC-2022。请帮忙。提前致谢！

Answer 1

按照您的ID条件为100，您可以尝试使用涉及日期的同一逻辑。这是一种可能将零值从日期转换为日期（任何日期）不等于31-Dec-2022。的一种可能的解决方案

WITH
    tbl AS
        (
            SELECT 123 "CUST_ID", 11 "DEPT", 10 "CODE1_ID", 20 "CODE2_ID", 100 "CODE3_ID", Null "CODE1_ID_STOP_DT", To_Date('31-OCT-2022', 'dd-MON-yyyy') "CODE2_ID_STOP_DT", To_Date('31-DEC-2022', 'dd-MON-yyyy') "CODE3_ID_STOP_DT" FROM DUAL UNION ALL
            SELECT 234 "CUST_ID", 21 "DEPT", NULL "CODE1_ID", 100 "CODE2_ID", 30 "CODE3_ID", Null "CODE1_ID_STOP_DT", NULL "CODE2_ID_STOP_DT", To_Date('31-DEC-2022', 'dd-MON-yyyy') "CODE3_ID_STOP_DT" FROM DUAL UNION ALL
            SELECT 345 "CUST_ID", 11 "DEPT", 100 "CODE1_ID", 30 "CODE2_ID", NULL "CODE3_ID", Null "CODE1_ID_STOP_DT", NULL "CODE2_ID_STOP_DT", To_Date('31-JUL-2022', 'dd-MON-yyyy') "CODE3_ID_STOP_DT" FROM DUAL UNION ALL
            SELECT 456 "CUST_ID", 31 "DEPT", 10 "CODE1_ID", 60 "CODE2_ID", 100 "CODE3_ID", To_Date('01-JAN-2023', 'dd-MON-yyyy') "CODE1_ID_STOP_DT", NULL "CODE2_ID_STOP_DT", To_Date('31-OCT-2022', 'dd-MON-yyyy') "CODE3_ID_STOP_DT" FROM DUAL 
        )   
Select * 
From tbl 
Where '100' in (Nvl(CODE1_id,0), Nvl(CODE2_id,0), Nvl(CODE3_id,0)) And
      To_Date('31-DEC-2022', 'dd-MON-yyyy') NOT IN (Nvl(CODE1_ID_STOP_DT, To_Date('01-JAN-2023', 'dd-MON-yyyy')),
                                                    Nvl(CODE2_ID_STOP_DT, To_Date('01-JAN-2023', 'dd-MON-yyyy')),
                                                    Nvl(CODE3_ID_STOP_DT, To_Date('01-JAN-2023', 'dd-MON-yyyy'))
                                                   )
--  
--  R e s u l t
--  
--     CUST_ID       DEPT   CODE1_ID   CODE2_ID   CODE3_ID CODE1_ID_STOP_DT CODE2_ID_STOP_DT CODE3_ID_STOP_DT
--  ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------------
--         345         11        100         30 Null       Null             Null             31-JUL-22        
--         456         31         10         60        100 01-JAN-23        Null             31-OCT-22

。被排除在外。
带有子句只是为了创建示例数据，而不是答案的一部分。
问候...