• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

Split query results from one row to 5 rows

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
bcarlis
Asked:
bcarlis
  • 6
  • 3
2 Solutions
 
lcohanDatabase AnalystCommented:
You need to use  a function like PIVOT/UNPIVOT:
http://www.oracle-developer.net/display.php?id=506
0
 
sdstuberCommented:
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
 
bcarlisAuthor Commented:
Good point Sean.. I'll check
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sdstuberCommented:
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
 
bcarlisAuthor Commented:
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
 
sdstuberCommented:
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
 
bcarlisAuthor Commented:
sorry wrong view
0
 
bcarlisAuthor Commented:
no that was right.. :)
0
 
bcarlisAuthor Commented:
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
 
bcarlisAuthor Commented:
Thank you Sean... works nice..

Thank you too lcohan...
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now