I'm stuck with this error. Please help me. Thank you
ORA-01722: invalid number
Sql script with pivot:
select 'Data'
||','||dtime_day
||','||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') dtime_day,
trunc(c.call_date, 'IW'),
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)
)
Output:
Data,03/06/2014,0,0,0,0,0,0,0,0,0,0
Data,03/27/2014,0,0,0,0,0,0,0,0,0,0
Data,04/03/2014,0,0,0,0,0,0,0,0,0,0
Data,04/26/2014,0,0,0,0,0,0,0,0,0,0
Instead of:
Data,02/17/2014,3387,2711,2294,2214,735,0,0,0,0,0
Data,02/24/2014,4244,3415,2833,2639,868,0,0,0,0,0
Data,03/03/2014,4860,3701,2998,2891,815,0,0,0,0,0
Data,03/10/2014,5698,4770,4206,3804,931,0,0,0,0,0
Data,03/17/2014,5914,4766,3945,3527,567,141,156,213,336,462
Data,03/24/2014,5818,4880,3958,2835,319,262,289,376,591,884
Data,03/31/2014,5724,4774,3877,3220,321,337,332,405,576,824
table 1 is contains date only
table 2
desc table2
Name Null Type
------------- -------- -------------
ID NUMBER(38)
DST_CHANNEL VARCHAR2(50)
CALL_DATE DATE
STATUS NOT NULL VARCHAR2(15)
ID STATUS CALL_DATE DST_CHANNEL
------ ---------------- --------------- -----------------------
1 ANSWERED 01/23/2014 SIP/item2-0000f5f3
2 NO ANSWER 01/26/2014 SIP/item3-00004354
. ........... ........... ...........
. ........... ........... ...........
. ........... ........... ...........