Skip to Main Content

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

fac586
Answer

I haven't figured out how to do it using declarative Open Region DAs, or the documented API. From hints here and here I've got a solution using the underlying widget methods.
Modify the card link icon HTML Expression to use a data attribute instead of an id:

<a href="#" class="t-open-popup" data-empno="#EMPNO#">
 <span class="fa fa-user" aria-hidden="true"></span>
</a>

This provides JavaScript with a simpler, more direct way to access the unique card key value.
Change the DA Execute JavaScript Code:

var empno = this.triggeringElement.dataset.empno,
    o = { autoOpen: true,
          parentElement: "[data-empno=" + empno + "]",
          callout: true,
          relativePosition: "after" };

$('#showDetails').popup(o);

See demo on page 586 of your app.

Marked as Answer by Veerendra Patil · May 20 2021
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 10 2014
Added on May 13 2014
16 comments
847 views