Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Error: Invalid number; using Pivot

2611484May 13 2014 — edited May 13 2014

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

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

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

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

Comments

Karthick2003

First step can you describe TABLE1 and TABLE2?

2611484

I already edit the question

Karthick2003 
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

I posted the desc of table

Karthick2003

>  and c.status like 'ANSWERED%'

I dont see STATUS column in Table2

2611484

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

Karthick2003

Ok got it, Change this

select 'Data'
       ||','||to_char(d.dtime_day,'MM/dd/yyyy')
       ||','||sg1_cnt

To

select 'Data'
       ||','||dtime_day 


       ||','||sg1_cnt

dtime_day is already converted in the inner query.

2611484

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

Karthick2003

> 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 
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.

Karthick2003

Those come from the PIVOT. That is fine.

2611484

i change the sql script, but i got still zero results

Karthick2003 
33795304-702b-48ff-b4ba-e896b1db94db wrote:




i change the sql script, but i got still zero results

That means some of your conditions are failing. As said already without knowing what's in the table cant help. Just check you join and where conditions.

padders

Just to be clear, are you asking us to tell you why a query we haven't seen returns no rows based on data we haven't seen?

1 person found this helpful
2611484

I just posted some data of each table coumn

2611484

I already edited my post - sql sript and added more info about table columns

1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 10 2014
Added on May 13 2014
#pivot, #query, #sql
16 comments
856 views