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!

How to transfer left join statement into pivot?

2611484May 12 2014 — edited May 13 2014

Please help me, I have a long script and I want it to be dynamically and flexible and I want to use pivot, so that I will not modified the script if ever there's a new item need to add, but I'm not familiar in pivot query in oracle. Thank you.

Here's the script:

select 'Data'
||','||to_char(d.dtime_day,'MM/dd/yyyy')
||','||nvl(ss1.total,0)
||','||nvl(ss2.total,0)
||','||nvl(ss3.total,0)
from table1 d
left join(
  
select trunc(call_date, 'IW') as ddate
  
,count(dst_channel) as total
  
from table2
  
where dst_channel like 'item1%'
  
and status like 'ANSWERED%'
  
group by trunc(call_date, 'IW')
) ss1 on d.dtime_day = ss1.ddate
left join(
  
select trunc(call_date, 'IW') as ddate
  
,count(dst_channel) as total
  
from table2
  
where dst_channel like 'item2%'
  
and status like 'ANSWERED%'
  
group by trunc(call_date, 'IW')
) ss2 on d.dtime_day = ss2.ddate
left join(
  
select trunc(call_date, 'IW') as ddate
  
,count(dst_channel) as total
  
from table2
  
where dst_channel like 'item3%'
  
and status like 'ANSWERED%'
  
group by trunc(call_date, 'IW')
 
) ss3 on d.dtime_day = ss3.ddate
where d.dtime_day between trunc(sysdate,'IW')-12*7 and trunc(sysdate) -1
and trunc(d.dtime_day, 'IW')= d.dtime_day
order by d.dtime_day;

Comments

LA County APEX

Isn't it the current record based on the row selected on the report or based on other pre-selected value from some where?

van Baal HR

yes. And that is exactly where the problem is located. There seems to be NO row selected.

LA County APEX

Supposed, your report region a static id is MYREP, create a dynamic action.

Event: After Refresh

Selection Type: Region

Region: your region

----

Add a true action.

Action: Execute JavaScript Code

Code:

$("#MYREP_ir tr").click(function() {

      $x('PX_REC_ID').value = this.cells[1].innerHTML;  //supposed the first column is the record id

      apex.widget.waitPopup();

      document.body.style.cursor = 'wait';

      apex.submit('BTN'); //use the button to redirect to popup a page

});

----

Create a branch.

Page or URL (Redirect)

When Button Pressed: BTN

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

Post Details

Locked on Jun 10 2014
Added on May 12 2014
1 comment
233 views