Solved

Split query results from one row to 5 rows

Posted on 2014-04-04
10
632 Views
Last Modified: 2014-04-04
Hi,
How can I change the below SQL returning one row showing all 5 aging values
ACCOUNT_NUMBER, PARTY_NAME, "0-30", "31-60", "61-90", "91-120", ">120"

 into 5 rows showing one age range each row?
 ACCOUNT_NUMBER, PARTY_NAME, "0-30"
 ACCOUNT_NUMBER, PARTY_NAME,  "31-60"
 ACCOUNT_NUMBER, PARTY_NAME,  "61-90"
 ACCOUNT_NUMBER, PARTY_NAME,  "91-120"
 ACCOUNT_NUMBER, PARTY_NAME,  ">120"

select 
  "ACCOUNT_NUMBER",
 "PARTY_NAME",
 "0-30",
 "31-60",
 "61-90",
 "91-120",
 ">120"
from AR_AGING_SUM_VW 
where CUST_ACCOUNT_ID = 1234 

Open in new window

0
Comment
Question by:bcarlis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 100 total points
ID: 39978770
You need to use  a function like PIVOT/UNPIVOT:
http://www.oracle-developer.net/display.php?id=506
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39978817
by any chance, is your data already unpivoted in the tables underlying the view?

can you use them and simply group by the criteria you need?

what I'm getting at is you might have a query that is doing a pivot just so you can unpivot when done.  That's clearly not efficient.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978825
Good point Sean.. I'll check
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 74

Accepted Solution

by:
sdstuber earned 400 total points
ID: 39978828
If you can't use the underlying tables, or if they are not an unpivoted source then try this....  (should work any version 9i or later.  Essentially the same as UNPIVOT but that requires 11g)


SELECT account_number,
       party_name,
       CASE WHEN n = 150 THEN '>120' ELSE (n - 30) || '-' || n END agerange,
       CASE
           WHEN n = 1 THEN "0-30"
           WHEN n = 2 THEN "31-60"
           WHEN n = 3 THEN "61-90"
           WHEN n = 4 THEN "91-120"
           ELSE ">120"
       END
           age
  FROM (SELECT "ACCOUNT_NUMBER",
               "PARTY_NAME",
               "0-30",
               "31-60",
               "61-90",
               "91-120",
               ">120"
          FROM ar_aging_sum_vw,
               (    SELECT LEVEL * 30 n
                      FROM DUAL
                CONNECT BY LEVEL <= 5)
         WHERE cust_account_id = 1234)
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978832
Wow, it is a bunch of case statements

select trx_number,due_date,CUSTOMER_ID,
       aging_between_0_30 "0-30",
       aging_between_31_60 "31-60",
       aging_between_61_90 "61-90",
       aging_between_91_120 "91-120",
       aging_greater_than_120 ">120",
       ((aging_between_0_30) + (aging_between_31_60) + ((aging_between_61_90) + (aging_between_91_120) + (aging_greater_than_120))) Total
       from (select trx_number,due_date,CUSTOMER_ID, sum(aging_between_0_30) aging_between_0_30,
               sum(aging_between_31_60) aging_between_31_60,
               sum(aging_between_61_90) aging_between_61_90,
               sum(aging_between_91_120) aging_between_91_120,
               sum(aging_greater_than_120) aging_greater_than_120        
          FROM (SELECT rcta.trx_number,apsa.due_date,apsa.CUSTOMER_ID,
                   (CASE
                         WHEN (SYSDATE - apsa.due_date) BETWEEN -100 AND 30 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_0_30,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) between 31 AND 60 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_31_60,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) between 61 AND 90 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_61_90,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) between 91 and 120 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_91_120,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) > 120 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_greater_than_120                     
                  from ra_customer_trx_all       rcta,
                       ar_payment_schedules_all  apsa
                 WHERE apsa.customer_trx_id      = rcta.customer_trx_id
                 --AND   rcta.bill_to_customer_id  = 3347
                 ) GROUP BY trx_number,due_date,CUSTOMER_ID)
                 WHERE ((aging_between_0_30) + (aging_between_31_60) + ((aging_between_61_90) + (aging_between_91_120) + (aging_greater_than_120))) > 0
                 ORDER BY 8 DESC;

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39978844
can you use the raw data or are you required (either by business rule or privileges) to use the view?

can we assume this?

trx_number = account_number
CUSTOMER_ID = party_name
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978845
sorry wrong view
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978856
no that was right.. :)
0
 
LVL 2

Author Comment

by:bcarlis
ID: 39978865
Beautiful...

SELECT account_number,
       PARTY_NAME,
       CASE WHEN n = 150 THEN '>120' ELSE (n - 30) || '-' || n END agerange,
       CASE
           WHEN n = 30 THEN "0-30"
           WHEN n = 60 THEN "31-60"
           WHEN n = 90 THEN "61-90"
           WHEN n = 120 THEN "91-120"
           ELSE ">120"
       END
           age
  FROM (SELECT "ACCOUNT_NUMBER",
               "PARTY_NAME",
               "0-30",
               "31-60",
               "61-90",
               "91-120",
               ">120", n
-- select * 
          FROM "APPS"."XXAPEX_DB_05_AR_AGING_SUM",
               (    SELECT LEVEL * 30 n
                      FROM DUAL
                CONNECT BY LEVEL <= 5)
         WHERE CUST_ACCOUNT_ID = 1007)

Open in new window

0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 39978871
Thank you Sean... works nice..

Thank you too lcohan...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question