CODEWITHSUNDEEP
sqloracle-database
IAM_SRK
IAM_SRK

Reputation: 1

How to find exact column name of a specific value in Oracle

I have a report requirement where the report should contain the rows that match the below criteria in a table (SALES) that have similar column names like

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

Sample data:

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

Criteria to fetch rows: where CODE is 100 and STOP_DT is not 31-DEC-2022 or null. I am able to fetch rows with code 100 as below.

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

But I am not able to write the SQL to fetch the rows of the CODE*_ID_STOP_DT columns where the value is NULL or not 31-DEC-2022. Please help. Thanks in advance!

Upvotes: 0

Views: 52

Answers (1)

d r
d r

Reputation: 7891

Following your condition for IDs with value of 100, you can try the same logic with dates involved. Here is the one possible solution with converting null values from dates into a date (any date) not equal to 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

This way the dates with value of 31-DEC-2022 or NULL will be excluded. The WITH clause is here just to create sample data and is not part of the answer.
Regards...

Upvotes: 3

Related Questions