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:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
|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.