SQL loop through columns with name to find max date

I’m looking for the SQL to loop through 20 columns with dates, to find the max.

My code is kind of bad now:

,case 
    when ANNEX_20_DATE is not null and ANNEX_20_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_20_DATE
    when ANNEX_19_DATE is not null and ANNEX_19_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_19_DATE
    when ANNEX_18_DATE is not null and ANNEX_18_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_18_DATE
    when ANNEX_17_DATE is not null and ANNEX_17_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_17_DATE
    when ANNEX_16_DATE is not null and ANNEX_16_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_16_DATE
    when ANNEX_15_DATE is not null and ANNEX_15_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_15_DATE
    when ANNEX_14_DATE is not null and ANNEX_14_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_14_DATE
    when ANNEX_13_DATE is not null and ANNEX_13_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_13_DATE
    when ANNEX_12_DATE is not null and ANNEX_12_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_12_DATE
    when ANNEX_11_DATE is not null and ANNEX_11_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_11_DATE
    when ANNEX_10_DATE is not null and ANNEX_10_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_10_DATE
    when ANNEX_9_DATE is not null and ANNEX_9_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_9_DATE
    when ANNEX_8_DATE is not null and ANNEX_8_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_8_DATE
    when ANNEX_7_DATE is not null and ANNEX_7_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_7_DATE
    when ANNEX_6_DATE is not null and ANNEX_6_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_6_DATE
    when ANNEX_5_DATE is not null and ANNEX_5_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_5_DATE
    when ANNEX_4_DATE is not null and ANNEX_4_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_4_DATE
    when ANNEX_3_DATE is not null and ANNEX_3_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_3_DATE
    when ANNEX_2_DATE is not null and ANNEX_2_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_2_DATE
    when ANNEX_1_DATE is not null and ANNEX_1_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_1_DATE
    
    ELSE 'N/A'

​x
 
,case     when ANNEX_20_DATE is not null and ANNEX_20_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_20_DATE    when ANNEX_19_DATE is not null and ANNEX_19_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_19_DATE    when ANNEX_18_DATE is not null and ANNEX_18_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_18_DATE    when ANNEX_17_DATE is not null and ANNEX_17_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_17_DATE    when ANNEX_16_DATE is not null and ANNEX_16_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_16_DATE    when ANNEX_15_DATE is not null and ANNEX_15_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_15_DATE    when ANNEX_14_DATE is not null and ANNEX_14_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_14_DATE    when ANNEX_13_DATE is not null and ANNEX_13_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_13_DATE    when ANNEX_12_DATE is not null and ANNEX_12_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_12_DATE    when ANNEX_11_DATE is not null and ANNEX_11_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_11_DATE    when ANNEX_10_DATE is not null and ANNEX_10_DATE<>to_date ('19000101', 'yyyy-mm-dd')            then ANNEX_10_DATE    when ANNEX_9_DATE is not null and ANNEX_9_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_9_DATE    when ANNEX_8_DATE is not null and ANNEX_8_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_8_DATE    when ANNEX_7_DATE is not null and ANNEX_7_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_7_DATE    when ANNEX_6_DATE is not null and ANNEX_6_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_6_DATE    when ANNEX_5_DATE is not null and ANNEX_5_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_5_DATE    when ANNEX_4_DATE is not null and ANNEX_4_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_4_DATE    when ANNEX_3_DATE is not null and ANNEX_3_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_3_DATE    when ANNEX_2_DATE is not null and ANNEX_2_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_2_DATE    when ANNEX_1_DATE is not null and ANNEX_1_DATE<>to_date ('19000101', 'yyyy-mm-dd')              then ANNEX_1_DATE        ELSE 'N/A'​

it’s oracle database, i konow that max date is in filed annex_20

Answer

First of all, you don’t need ANNEX_XX_DATE is not null since ANNEX_XX_DATE<>... can’t be true if ANNEX_XX_DATE is null, so you can make it easier without it:

,case 
    when ANNEX_20_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_20_DATE
    when ANNEX_19_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_19_DATE
    when ANNEX_18_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_18_DATE
    when ANNEX_17_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_17_DATE
    when ANNEX_16_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_16_DATE
    when ANNEX_15_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_15_DATE
    when ANNEX_14_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_14_DATE
    when ANNEX_13_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_13_DATE
    when ANNEX_12_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_12_DATE
    when ANNEX_11_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_11_DATE
    when ANNEX_10_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_10_DATE
    when ANNEX_9_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_9_DATE
    when ANNEX_8_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_8_DATE
    when ANNEX_7_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_7_DATE
    when ANNEX_6_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_6_DATE
    when ANNEX_5_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_5_DATE
    when ANNEX_4_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_4_DATE
    when ANNEX_3_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_3_DATE
    when ANNEX_2_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_2_DATE
    when ANNEX_1_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_1_DATE
    
    ELSE 'N/A'

 
,case     when ANNEX_20_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_20_DATE    when ANNEX_19_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_19_DATE    when ANNEX_18_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_18_DATE    when ANNEX_17_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_17_DATE    when ANNEX_16_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_16_DATE    when ANNEX_15_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_15_DATE    when ANNEX_14_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_14_DATE    when ANNEX_13_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_13_DATE    when ANNEX_12_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_12_DATE    when ANNEX_11_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_11_DATE    when ANNEX_10_DATE<>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_10_DATE    when ANNEX_9_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_9_DATE    when ANNEX_8_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_8_DATE    when ANNEX_7_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_7_DATE    when ANNEX_6_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_6_DATE    when ANNEX_5_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_5_DATE    when ANNEX_4_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_4_DATE    when ANNEX_3_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_3_DATE    when ANNEX_2_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_2_DATE    when ANNEX_1_DATE <>to_date ('19000101', 'yyyy-mm-dd') then ANNEX_1_DATE        ELSE 'N/A'​

Also I would probably replace it with coalesce(nullif(…)…) to make it easier and shorter:

coalesce(
   nullif(ANNEX_20_DATE, date'1900-01-01'),
   nullif(ANNEX_19_DATE, date'1900-01-01'),
   nullif(ANNEX_18_DATE, date'1900-01-01'),
   nullif(ANNEX_17_DATE, date'1900-01-01'),
   nullif(ANNEX_16_DATE, date'1900-01-01'),
   nullif(ANNEX_15_DATE, date'1900-01-01'),
   nullif(ANNEX_14_DATE, date'1900-01-01'),
   nullif(ANNEX_13_DATE, date'1900-01-01'),
   nullif(ANNEX_12_DATE, date'1900-01-01'),
   nullif(ANNEX_11_DATE, date'1900-01-01'),
   nullif(ANNEX_10_DATE, date'1900-01-01'),
   nullif(ANNEX_9_DATE , date'1900-01-01'),
   nullif(ANNEX_8_DATE , date'1900-01-01'),
   nullif(ANNEX_7_DATE , date'1900-01-01'),
   nullif(ANNEX_6_DATE , date'1900-01-01'),
   nullif(ANNEX_5_DATE , date'1900-01-01'),
   nullif(ANNEX_4_DATE , date'1900-01-01'),
   nullif(ANNEX_3_DATE , date'1900-01-01'),
   nullif(ANNEX_2_DATE , date'1900-01-01'),
   nullif(ANNEX_1_DATE , date'1900-01-01')
)

 
coalesce(   nullif(ANNEX_20_DATE, date'1900-01-01'),   nullif(ANNEX_19_DATE, date'1900-01-01'),   nullif(ANNEX_18_DATE, date'1900-01-01'),   nullif(ANNEX_17_DATE, date'1900-01-01'),   nullif(ANNEX_16_DATE, date'1900-01-01'),   nullif(ANNEX_15_DATE, date'1900-01-01'),   nullif(ANNEX_14_DATE, date'1900-01-01'),   nullif(ANNEX_13_DATE, date'1900-01-01'),   nullif(ANNEX_12_DATE, date'1900-01-01'),   nullif(ANNEX_11_DATE, date'1900-01-01'),   nullif(ANNEX_10_DATE, date'1900-01-01'),   nullif(ANNEX_9_DATE , date'1900-01-01'),   nullif(ANNEX_8_DATE , date'1900-01-01'),   nullif(ANNEX_7_DATE , date'1900-01-01'),   nullif(ANNEX_6_DATE , date'1900-01-01'),   nullif(ANNEX_5_DATE , date'1900-01-01'),   nullif(ANNEX_4_DATE , date'1900-01-01'),   nullif(ANNEX_3_DATE , date'1900-01-01'),   nullif(ANNEX_2_DATE , date'1900-01-01'),   nullif(ANNEX_1_DATE , date'1900-01-01')) ​

Advertisement

Answer