Forum Stats

  • 3,854,961 Users
  • 2,264,441 Discussions
  • 7,905,847 Comments

Discussions

Error: Invalid number; using Pivot

2611484
2611484 Member Posts: 29
edited May 13, 2014 6:36AM in SQL & PL/SQL

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'),

<span class="pln" style="font-size: 13.333333969116211px;"><br/>  c</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dst_channel<br/></span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">from</span><span class="pln" style="font-size: 13.333333969116211px;"> table1 d<br/></span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">left</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">join</span><span class="pln" style="font-size: 13.333333969116211px;"> table2 c<br/></span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">on</span><span class="pln" style="font-size: 13.333333969116211px;"> c</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">call_date </span><span class="pun" style="font-size: 13.333333969116211px;">>=</span><span class="pln" style="font-size: 13.333333969116211px;"> d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day<br/></span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">and</span><span class="pln" style="font-size: 13.333333969116211px;"> c</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">status </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">like</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'ANSWERED%'</span><span class="pln" style="font-size: 13.333333969116211px;"><br/></span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">where</span><span class="pln" style="font-size: 13.333333969116211px;"> d</span><span class="pun" style="font-size: 13.333333969116211px;">.</span><span class="pln" style="font-size: 13.333333969116211px;">dtime_day </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">between</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">sysdate</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'IW'</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="lit" style="font-size: 13.333333969116211px; color: #800000;">-12</span><span class="pun" style="font-size: 13.333333969116211px;">*</span><span class="lit" style="font-size: 13.333333969116211px; color: #800000;">7</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">and</span><span class="pln" style="font-size: 13.333333969116211px;"> trunc</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">sysdate</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="lit" style="font-size: 13.333333969116211px; color: #800000;">-1</span><span class="pln" style="font-size: 13.333333969116211px;"><br/></span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"><br/></span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">pivot</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">count</span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="pln" style="font-size: 13.333333969116211px;">dst_channel</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> cnt<br/></span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">for</span><span class="pln" style="font-size: 13.333333969116211px;"> dst_channel </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">in</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="pun" style="font-size: 13.333333969116211px;">(</span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item01'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg1</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item02'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg2</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item03'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg3</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item04'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg4</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item05'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg5</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item06'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg6</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item07'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg7</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item08'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg8</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item09'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg9</span><span class="pun" style="font-size: 13.333333969116211px;">,</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>   </span><span class="str" style="font-size: 13.333333969116211px; color: #800000;">'SIP/item10'</span><span class="pln" style="font-size: 13.333333969116211px;"> </span><span class="kwd" style="font-size: 13.333333969116211px; color: #00008b;">as</span><span class="pln" style="font-size: 13.333333969116211px;"> sg10</span><span class="pun" style="font-size: 13.333333969116211px;">)</span><span class="pln" style="font-size: 13.333333969116211px;"><br/>  </span><span class="pun" style="font-size: 13.333333969116211px;">)</span>


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

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

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

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

Tagged:
Karthick2003
«1

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    First step can you describe TABLE1 and TABLE2?

  • 2611484
    2611484 Member Posts: 29

    I already edit the question

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    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
    2611484 Member Posts: 29

    I posted the desc of table

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    >  and c.status like 'ANSWERED%'

    I dont see STATUS column in Table2

  • 2611484
    2611484 Member Posts: 29

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

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    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>
    

    <span style="font-family: arial,helvetica,sans-serif;">To</span>

    <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
    2611484 Member Posts: 29

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

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    > 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
    Moazzam Member Posts: 1,356 Silver Trophy
    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.

This discussion has been closed.