SELECT /*+ Index (C cdr_sum_dte) */ x.customer, x.srpid, COUNT(distinct c.pin) PIN_CNT, NVL(d.carrier,'Unknown') CARRIER, c.msgtype, NVL(d.device_type,'Unknown') DEVICE_TYPE FROM BBLG.CDR_SUMMARY@PRLDW C, BBLG.DEVICE_INFO@PRLDW D, (SELECT /*+ Index (S sio_uk) */ s.id, s.srpid, f.customer FROM BBLG.SRP_INFO@PRLDW s, FINAL_CUSTOMER_LIST f WHERE s.srpid=f.srp_id AND f.c_source in ('SHIP_TO', 'T_SUPPORT') )x WHERE c.dte between to_date('01-Dec-2003') AND to_date('01-Dec-2003') AND c.srpid_id = x.id AND c.pin = d.pin GROUP BY x.customer, x.srpid, d.carrier,c.msgtype, d.device_type
In order to achieve your objective you cannot strictly use SQL. You have to use REF CURSOR to return your result through a PL/SQL package. Below, I have provided a possible solution for your query.
The query output will be as follows:
|CUSTOMER||SRPID||PIN_CNT||CARRIER||MSGTYPE||Device 1||...||Device n|
However, I do not know whether you want to have the result as follows
|Attribute||Device 1||...||Device n|
|SRPID||SRPTID 1||...||SRPID n|
|PIN_CNT||PIN_CNT 1||...||PIN_CNT n|
|CARRIER||CARRIER 1||...||CARRIER n|
|MSGTYPE||MSGTYPE 1||...||MSG_TYPE n|
The solution provided on the next page is for the first one. However, if your desired output is the latter, and while I am working on a solution similar to the one one the next page for the second requirement, you can try another solution: You may modify the program to dynamically create a temporary table with one column called "Attribute." Then for each entry in DEVICE_INFO table you can alter the temporary table to add the column named as the DEVICE_TYPE value in DEVICE_INFO table. Now, using your original SQL as an IN REF_CURSOR parameter you can use PL/SQL logic to populate the temporary table accordingly. Now, you can use a ref_cursor variable and open it as SELECT * from the temporary table. The desired outcome will be similar to that of Outcome #2 above.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Azim Fahmi
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.