Joint use of case when and Max

Case when... then and decode have been used for a long time, and they always thought that case when can only be used independently or together with sum. In the test, a vertical row of data is merged into a model row, this row is not a numerical value.

I searched a lot on the Internet but did not find the combined use of the non-numeric type, but this is not difficult, as long as it can be used with Max.

Here are two types of examples:

Example 1: Combined use of decode and Max

Select distinct emp_no, max (decode (to_char (work_date, 'dd'), '01', card_frequency) d01,
Max (decode (to_char (work_date, 'dd'), '02', card_frequency) d02,
Max (decode (to_char (work_date, 'dd'), '03', card_frequency) d03,
Max (decode (to_char (work_date, 'dd'), '04 ', card_frequency) d04,
Max (decode (to_char (work_date, 'dd'), '05 ', card_frequency) d05,
Max (decode (to_char (work_date, 'dd'), '06', card_frequency) d06,
Max (decode (to_char (work_date, 'dd'), '07', card_frequency) d07,
Max (decode (to_char (work_date, 'dd'), '08', card_frequency) d08,
Max (decode (to_char (work_date, 'dd'), '09', card_frequency) d09,
Max (decode (to_char (work_date, 'dd'), '10', card_frequency) D10,
Max (decode (to_char (work_date, 'dd'), '11', card_frequency) D11,
Max (decode (to_char (work_date, 'dd'), '12', card_frequency) D12,
Max (decode (to_char (work_date, 'dd'), '13', card_frequency) D13,
Max (decode (to_char (work_date, 'dd'), '14', card_frequency) D14,
Max (decode (to_char (work_date, 'dd'), '15', card_frequency) D15,
Max (decode (to_char (work_date, 'dd'), '16', card_frequency) D16
From hra_read_card_month
Where emp_no = '20160301' and to_char (work_date, 'yyyy/mm') = '20160301'
Group by emp_no

 

Example 2: joint use of case when and Max

Select emp_no, max (Case work_date when to_date ('1970-03-01 ', 'yyyy-mm-dd') Then card_frequency end) as d01,
Max (Case work_date when to_date ('1970-03-02 ', 'yyyy-mm-dd') Then card_frequency end) as d02,
Max (Case work_date when to_date ('1970-03-03 ', 'yyyy-mm-dd') Then card_frequency end) as d03,
Max (Case work_date when to_date ('1970-03-04 ', 'yyyy-mm-dd') Then card_frequency end) as d04,
Max (Case work_date when to_date ('1970-03-05 ', 'yyyy-mm-dd') Then card_frequency end) as d05,
Max (Case work_date when to_date ('1970-03-06 ', 'yyyy-mm-dd') Then card_frequency end) as d06,
Max (Case work_date when to_date ('1970-03-07 ', 'yyyy-mm-dd') Then card_frequency end) as d07,
Max (Case work_date when to_date ('1970-03-08 ', 'yyyy-mm-dd') Then card_frequency end) as d08,
Max (Case work_date when to_date ('1970-03-09 ', 'yyyy-mm-dd') Then card_frequency end) as d09,
Max (Case work_date when to_date ('1970-03-10 ', 'yyyy-mm-dd') Then card_frequency end) as D10,
Max (Case work_date when to_date ('1970-03-11 ', 'yyyy-mm-dd') Then card_frequency end) as D11,
Max (Case work_date when to_date ('1970-03-12 ', 'yyyy-mm-dd') Then card_frequency end) as D12,
Max (Case work_date when to_date ('1970-03-13 ', 'yyyy-mm-dd') Then card_frequency end) as D13,
Max (Case work_date when to_date ('1970-03-14 ', 'yyyy-mm-dd') Then card_frequency end) as D14,
Max (Case work_date when to_date ('1970-03-15 ', 'yyyy-mm-dd') Then card_frequency end) as D15,
Max (Case work_date when to_date ('1970-03-16 ', 'yyyy-mm-dd') Then card_frequency end) as D16
From hra_read_card_month where emp_no = '201312'
Group by emp_no;

The difference between instance 1 and instance 2 lies in the use of the decode and case when syntax, and the difference in the date placement location. if you remove Max, multiple rows are displayed, and the results are the same.