Results 1 to 2 of 2
  1. #1
    Join Date
    May 2014
    Posts
    2

    Unanswered: Invalid Identifier with pivot query

    Hello! Im stuck with this error "ORA-00904: "D"."DTIME_DAY": invalid identifier" but I don't know how to fix that error, since d.dtime is already identified. Please help me. Thank you

    select 'Data'
    ||','||to_char(d.dtime_day,'MM/dd/yyyy')
    ||','||sg1_cnt
    ||','||sg2_cnt
    ||','||sg3_cnt
    ||','||sg4_cnt
    ||','||sg5_cnt
    ||','||sg6_cnt
    ||','||sg7_cnt
    ||','||sg8_cnt
    ||','||sg9_cnt
    ||','||sg10_cnt
    from (
    select 'Data',
    to_char(d.dtime_day,'MM/dd/yyyy'),
    trunc(c.call_date, 'IW') as ddate,
    c.dst_channel
    from table1 d
    left join table2 c
    on c.call_date >= d.dtime_day
    and c.status like 'ANSWERED%'
    where d.dtime_day between trunc(sysdate,'IW')-12*7 and trunc(sysdate) -1
    )
    pivot (count(dst_channel) as cnt
    for dst_channel in ('SIP/item01' as sg1,
    'SIP/item02' as sg2,
    'SIP/item03' as sg3,
    'SIP/item04' as sg4,
    'SIP/item05' as sg5,
    'SIP/item06' as sg6,
    'SIP/item07' as sg7,
    'SIP/item08' as sg8,
    'SIP/item09' as sg9,
    'SIP/item10' as sg10)
    )

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hello,

    the error is quite self-explanatory, especially if you would state the place it points to (Oracle does it). Apparently, it is the second row in the outer query, as there is no column named DTIME_DAY in the inner query. Moreover, the subquery is not aliased, so using alias D is also wrong.

    As you aliased DDATE in the inner query (although you are not using it), what about doing the same with the expression in the row above it? Applying TO_CHAR only once would be sufficient and correct.

    Just have a look to some sample on DUAL table:
    Code:
    SQL> select d.dummy from ( select nvl(d.dummy,'N') from dual d );
    select d.dummy from ( select nvl(d.dummy,'N') from dual d )
           *
    ERROR at line 1:
    ORA-00904: "D"."DUMMY": invalid identifier
    
    
    SQL> -- missing column alias in the inner query
    SQL> select dummy from ( select nvl(d.dummy,'N') from dual d );
    select dummy from ( select nvl(d.dummy,'N') from dual d )
           *
    ERROR at line 1:
    ORA-00904: "DUMMY": invalid identifier
    
    
    SQL> -- missing alias of the inner query
    SQL> select d.dummy from ( select nvl(d.dummy,'N') dummy from dual d );
    select d.dummy from ( select nvl(d.dummy,'N') dummy from dual d )
           *
    ERROR at line 1:
    ORA-00904: "D"."DUMMY": invalid identifier
    
    
    SQL> -- not using alias of the inner query
    SQL> select dummy from ( select nvl(d.dummy,'N') dummy from dual d );
    
    D
    -
    X
    
    
    SQL> -- using alias of the inner query
    SQL> select x.dummy from ( select nvl(d.dummy,'N') dummy from dual d ) x;
    
    D
    -
    X

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •