oracle - sql UNPIVOT query date format

Question

I have a query with UnPivot as follows. This will give me code, value1, value2. Now i want to change the format of the date fields as 'MM/DD/YYYY'. Tried to_char(MATH_DATE, 'MM/DD/YYYY') but getting

ORA-00904: "from$_subquery$_001"."MATH_DATE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

select
            * 
        from
            ( select
                MATH,
                to_char(MATH_DATE,'MM/DD/YYYY'),
                SCI,
                SCI_DATE,
                HIST,
                HIST_DATE,
                GEO,
                GEO_DATE,
                PE,
                PE_DATE 
            from
                subj_view vw,
                inventory bi  
            where
                bi.id = vw.id  
                and id = 161) UNPIVOT INCLUDE NULLS((value1,
            value2)  FOR code in( (MATH,
            math_date) as 'MATH',
            (SCI,
            SCI_DATE) as 'SCI',
            (HIST,
            HIST_DATE) as 'HIST',
            (GEO,
            GEO_DATE) as 'GEO',
            (PE,
            PE_DATE) as 'PE' ) );

Output looks like

code    value1  value2
MATH    100     20070401
SCI     86  
HIST    89      201904
GEO     89      20191206
PE      90      20070118

How can I change the formatof valaue2 with the same query. Please help.

Any suggestions highly appreciated.

Answer 1

The names of your columns must match with the names used in the for ... in clause. As you have not given an alias to the to_char(), there is no longer a match_date column.

So use an alias; it can be the same as the original column name, so that you don't need to change the for ... in clause:

 to_char(MATH_DATE,'MM/DD/YYYY') math_date,