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

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

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 person found this helpful

1 - 1

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 10 2014

Added on May 13 2014

#pivot, #query, #sql

16 comments

847 views

SQL & PL/SQL

Error: Invalid number; using Pivot

Comments

Post Details