Error: Invalid number; using Pivot

2611484 · May 13, 2014 4:03AM

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

. ........... ........... ...........

Karthick2003 · May 13, 2014 4:25AM

First step can you describe TABLE1 and TABLE2?

2611484 · May 13, 2014 4:36AM

I already edit the question

Karthick2003 · May 13, 2014 4:40AM

Describe means like this

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER
 ENAME                                              VARCHAR2(6)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER
 HIREDATE                                           DATE
 SAL                                                NUMBER
 COM                                                NUMBER
 DEPTNO                                             NUMBER

SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER
 DNAME                                              VARCHAR2(10)
 LOC                                                VARCHAR2(8)

SQL>

2611484 · May 13, 2014 4:45AM

I posted the desc of table

Karthick2003 · May 13, 2014 5:00AM

> and c.status like 'ANSWERED%'

I dont see STATUS column in Table2

2611484 · May 13, 2014 5:07AM

sorry i accidentally erase, now there's a status table

Karthick2003 · May 13, 2014 5:10AM

Ok got it, Change this

<span style="color: #000000; font-size: 13.33px; font-family: courier new,courier;">select</span><span style="font-family: courier new,courier;"><span class="pln" style="font-size: 13.33px;"> </span><span class="str" style="font-size: 13.33px;">'Data'</span><span class="pln" style="font-size: 13.33px;"><br/></span><span class="pun" style="font-size: 13.33px;">       <span style="color: #ff0000;">||</span></span><span style="color: #ff0000;"><span class="str" style="font-size: 13.33px;">','</span><span class="pun" style="font-size: 13.33px;">||</span><span class="pln" style="font-size: 13.33px;">to_char</span><span class="pun" style="font-size: 13.33px;">(</span><span class="pln" style="font-size: 13.33px;">d</span><span class="pun" style="font-size: 13.33px;">.</span><span class="pln" style="font-size: 13.33px;">dtime_day</span><span class="pun" style="font-size: 13.33px;">,</span><span class="str" style="font-size: 13.33px;">'MM/dd/yyyy'</span></span><span style="color: #ff0000; font-size: 13.33px;">)</span><span class="pln" style="font-size: 13.33px;"><br/></span><span class="pun" style="font-size: 13.33px;">       ||</span><span class="str" style="font-size: 13.33px;">','</span><span class="pun" style="font-size: 13.33px;">||</span></span><span style="color: #000000; font-size: 13.33px; font-family: courier new,courier;">sg1_cnt</span>

To

<span class="pln" style="font-size: 13.33px;"><span style="font-family: Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif; font-size: 13.33px;"><code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; font-size: 13.33px;"><span style="color: #000000; font-size: 13.33px; font-family: courier new,courier;">select</span><span style="font-family: courier new,courier;"><span class="pln" style="font-size: 13.33px;"> </span><span class="str" style="font-size: 13.33px;">'Data'</span><span class="pln" style="font-size: 13.33px;"><br/></span><span class="pun" style="font-size: 13.33px;">       <span style="color: #ff0000;">||</span></span><span style="color: #ff0000;"><span class="str" style="font-size: 13.33px;">','</span><span class="pun" style="font-size: 13.33px;">||</span><span class="pln" style="font-size: 13.33px;">dtime_day</span><span class="pun" style="font-size: 13.33px;"> </span></span></span>
<span class="pln" style="font-size: 13.33px;"><span style="color: #000000; font-family: Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif; font-size: 13.33px;"><code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; font-size: 13.33px;"><span style="font-family: courier new,courier;"><span class="pun" style="font-size: 13.33px;">       ||</span><span class="str" style="font-size: 13.33px;">','</span><span class="pun" style="font-size: 13.33px;">||</span></span><span style="color: #000000; font-size: 13.33px; font-family: courier new,courier;">sg1_cnt</span>

dtime_day is already converted in the inner query.

2611484 · May 13, 2014 5:16AM

i changed it already but i got zero results only, which is not right. each item have large total of result

Karthick2003 · May 13, 2014 5:27AM

> i changed it already

Should I use my crystal ball to find it out?

> but i got zero results only, which is not right. each item have large total of result

Without your data i cant do much.

Moazzam · May 13, 2014 5:44AM

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

In the above select statement, which tables contains the columns (sg1_cnt, sg2_cnt, sg3_cnt.....etc), I think by removing these columns from SELECT shall fix the issue.

Forum Stats

Quick Links

Categories

Error: Invalid number; using Pivot

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Error: Invalid number; using Pivot

Answers