Solved

Query Output

Posted on 2013-05-29
15
261 Views
Last Modified: 2013-06-11
Hi,
I have my source data and would need to display the output in a particular format.

I have detailed the source and output in the attached file.

Can someone please help?

Thanks
File.xlsx
0
Comment
Question by:MRPT
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Using what tool?

Most tools ( even sqlplus ) have reporting capability.

Or are you looking to generate this using straight SQL?
0
 

Author Comment

by:MRPT
Comment Utility
Yes, using straight SQL intially. Later try to put it in OBIEE.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
is your source really a table or is it the results of a query?

if a query, it would probably be better to revamp the query to return the results in the form you want.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
assuming your data really is coming from a table, or you if you don't have the option to redo a source query then try this...



SELECT   CASE WHEN cnt > 1 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
         mgr_1 || CASE WHEN cnt > 1 AND role = 'MGR1' THEN ' Total' END mgr_1,
         CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
         amount rep_amount,
         MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1) mgr_1_amount,
         (SELECT amount
            FROM sourcetable
           WHERE role = 'MGR2')
             mgr_2_amount
    FROM (SELECT s.*, COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt
            FROM sourcetable s) s
   WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END

Open in new window



basic idea is to pick apart all of the summations and grouping in the source and then sort and reassemble them into the form you requested.

if the data was not already summed and grouped, this could probably be done with cube and rollup grouping much easier
0
 

Author Comment

by:MRPT
Comment Utility
@sdstuber: yes, my data is really coming as a table.
I will try the query you sent.

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
this looks absurdly complex, but it gets very close to the expected results:
MGR_2     MGR_1       REP    REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
ABBAGG    DUKET       MAGUIL1   278394    5663975    30182016
          DUKET Total           278394    5663975    30182016
ABBAGG    FOLKC       CALLAM5   959966    4488813    30182016
ABBAGG    FOLKC       WALLIJ1   349777    4488813    30182016
ABBAGG    FOLKC       WRIGHG5   850205    4488813    30182016
          FOLKC Total           2159948   4488813    30182016
ABBAGG    FOLTZT      HEARNT    699553    4964696    30182016
ABBAGG    FOLTZT      GRAFTB    328362    4964696    30182016
          FOLTZT Total          1027915   4964696    30182016
ABBAGG    REYNAF      HARROB    765465    5303996    30182016
ABBAGG    REYNAF      CALDWD    856596    5303996    30182016
          REYNAF Total          1622061   5303996    30182016
ABBAGG    VANBRJ                6325879   6325879    30182016

Open in new window

and (dare I post it?), the "code"
WITH
sqf1 AS (
          SELECT
            mgr2.mgr_2
          , mgr1.mgr_1
          , rep.rep
          , CASE WHEN rep.rep IS NULL THEN mgr_1_amount ELSE rep_amount END rep_amount
          , mgr_1_amount
          , mgr_2_amount
          FROM (SELECT mgr_2, amount AS mgr_2_amount FROM source_table WHERE "RESOURCE" = mgr_2) mgr2
          LEFT JOIN (SELECT mgr_2, mgr_1, amount AS mgr_1_amount FROM source_table WHERE "RESOURCE" = mgr_1) mgr1
            ON mgr2.mgr_2 = mgr1.mgr_2
          LEFT JOIN (SELECT mgr_1, "RESOURCE" AS rep, amount AS rep_amount FROM source_table WHERE ROLE= 'REP') rep
            ON mgr1.mgr_1 = rep.mgr_1
          WHERE mgr_1_amount <> mgr_2_amount
          ORDER BY
            mgr2.mgr_2
          , mgr1.mgr_1
          , rep.rep
        )
, sqf2 AS (
          SELECT
            mgr_2
          , mgr_1 || ' Total' AS label
          , NULL
          , sum(CASE WHEN rep IS NULL THEN mgr_1_amount ELSE rep_amount END) rep_amount
          , max(mgr_1_amount)
          , max(mgr_2_amount)
          FROM sqf1
          WHERE rep IS NOT NULL
          GROUP BY GROUPING SETS (mgr_2, mgr_1)
          HAVING mgr_1 IS NOT NULL
         )
SELECT
*
FROM sqf1
UNION ALL
SELECT
*
FROM sqf2
ORDER BY 2
;

Open in new window

see it at: http://sqlfiddle.com/#!4/8dfac/3 I'm sure there has to be an easier way
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
easier way posted above
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
yes, here are the results of that, after adding to the order by
MGR_2     MGR_1        REP    REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
ABBAGG    DUKET        MAGUIL1   278394    5663975    30182016
ABBAGG    DUKET                  5663975   5663975    30182016 -- expected total (null) & line different
ABBAGG    FOLKC        CALLAM5   959966    4488813    30182016
ABBAGG    FOLKC        WALLIJ1   349777    4488813    30182016
ABBAGG    FOLKC        WRIGHG5   850205    4488813    30182016
          FOLKC Total            4488813   4488813    30182016 -- expected total (2159948)
ABBAGG    FOLTZT       HEARNT    699553    4964696    30182016
ABBAGG    FOLTZT       GRAFTB    328362    4964696    30182016
          FOLTZT Total           4964696   4964696    30182016 -- expected total (1027915)
ABBAGG    REYNAF       HARROB    765465    5303996    30182016
ABBAGG    REYNAF       CALDWD    856596    5303996    30182016
          REYNAF Total           5303996   5303996    30182016 -- expected total (1622061)
ABBAGG    VANBRJ                 6325879   6325879    30182016

-- the bizzarre way
MGR_2     MGR_1       REP    REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
ABBAGG    DUKET       MAGUIL1   278394    5663975    30182016
          DUKET Total           278394    5663975    30182016 -- expected total (null)
ABBAGG    FOLKC       CALLAM5   959966    4488813    30182016
ABBAGG    FOLKC       WALLIJ1   349777    4488813    30182016
ABBAGG    FOLKC       WRIGHG5   850205    4488813    30182016
          FOLKC Total           2159948   4488813    30182016
ABBAGG    FOLTZT      HEARNT    699553    4964696    30182016
ABBAGG    FOLTZT      GRAFTB    328362    4964696    30182016
          FOLTZT Total          1027915   4964696    30182016
ABBAGG    REYNAF      HARROB    765465    5303996    30182016
ABBAGG    REYNAF      CALDWD    856596    5303996    30182016
          REYNAF Total          1622061   5303996    30182016
ABBAGG    VANBRJ                6325879   6325879    30182016

Open in new window

 (hence the reason for looking at it) http://sqlfiddle.com/#!4/99d5b/5
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
wow! thanks for the heads up.

I really wasn't paying attention to the output, I missed all sorts of things.
Fortunately, they are small, and the fixes to the original query are likewise trivial

SELECT   CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
         mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
         CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
         CASE
             WHEN role = 'REP'
             THEN
                 amount
             WHEN role = 'MGR1' AND cnt > 1
             THEN
                 SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
             WHEN cnt = 0
             THEN
                 amount
         END
             rep_amount,
         MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1) mgr_1_amount,
         (SELECT amount
            FROM sourcetable
           WHERE role = 'MGR2')
             mgr_2_amount
    FROM (SELECT s.*, COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt
            FROM sourcetable s) s
   WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;

Open in new window


thanks again for catching my mistakes


SQL> SELECT   CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
  2           mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
  3           CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
  4           CASE
  5               WHEN role = 'REP'
  6               THEN
  7                   amount
  8               WHEN role = 'MGR1' AND cnt > 1
  9               THEN
 10                   SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
 11               WHEN cnt = 0
 12               THEN
 13                   amount
 14           END
 15               rep_amount,
 16           MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1) mgr_1_amount,
 17           (SELECT amount
 18              FROM sourcetable
 19             WHERE role = 'MGR2')
 20               mgr_2_amount
 21      FROM (SELECT s.*, COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt
 22              FROM sourcetable s) s
 23     WHERE role != 'MGR2'
 24  ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;

MGR_2  MGR_1        REP     REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
------ ------------ ------- ---------- ------------ ------------
ABBAGG DUKET        MAGUIL1     278394      5663975     30182016
       DUKET Total                          5663975     30182016
ABBAGG FOLKC        CALLAM5     959966      4488813     30182016
ABBAGG FOLKC        WALLIJ1     349777      4488813     30182016
ABBAGG FOLKC        WRIGHG5     850205      4488813     30182016
       FOLKC Total             2159948      4488813     30182016
ABBAGG FOLTZT       GRAFTB      328362      4964696     30182016
ABBAGG FOLTZT       HEARNT      699553      4964696     30182016
       FOLTZT Total            1027915      4964696     30182016
ABBAGG REYNAF       CALDWD      856596      5303996     30182016
ABBAGG REYNAF       HARROB      765465      5303996     30182016
       REYNAF Total            1622061      5303996     30182016
ABBAGG VANBRJ                  6325879      6325879     30182016

13 rows selected.

SQL>

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
:) knew there would be a better way,
mind you I'm not a great fan of using sql for this style of presentation
- although I do use group by rollup often.

{blast, bad typo, fixed - sorry}
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
slightly more efficient version, eliminating extra io to the table


SELECT   CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
         mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
         CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
         CASE
             WHEN role = 'REP'
             THEN
                 amount
             WHEN role = 'MGR1' AND cnt > 1
             THEN
                 SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
             WHEN cnt = 0
             THEN
                 amount
         END
             rep_amount,
         mgr_1_amount,
         mgr_2_amount
    FROM (SELECT s.*,
                 COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt,
                 MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
                     mgr_1_amount,
                 MAX(CASE WHEN role = 'MGR2' THEN amount END) OVER (PARTITION BY mgr_2) mgr_2_amount
            FROM sourcetable s) s
   WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
v.neat, nice brief explain plan too!
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
and finally, a (hopefully) more logical arrangement.
Exact same steps as my previous query, but I've replaced the negative conditions with postive
e.g. instead of role != 'MGR1'  it is now  role = 'REP'
I also consolidated the analytics in one place, making the formatting portion more compact

So, this version should not only be the most efficient; but I hope the most understandable/maintainable going forward


SELECT   CASE WHEN role = 'MGR1' AND repcnt > 0 THEN NULL ELSE mgr_2 END mgr_2,
         mgr_1 || CASE WHEN role = 'MGR1' AND repcnt > 0 THEN ' Total' END mgr_1,
         CASE WHEN role = 'REP' THEN rep END rep,
         CASE
             WHEN role = 'REP' THEN amount
             WHEN role = 'MGR1' AND repcnt = 0 THEN amount
             WHEN role = 'MGR1' AND repcnt > 1 THEN rep_total
         END
             rep_amount,
         mgr_1_amount,
         mgr_2_amount
    FROM (SELECT s.*,
                 COUNT(CASE WHEN role = 'REP' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) repcnt,
                 SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
                     rep_total,
                 MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
                     mgr_1_amount,
                 MAX(CASE WHEN role = 'MGR2' THEN amount END) OVER (PARTITION BY mgr_2) mgr_2_amount
            FROM sourcetable s) s
   WHERE role IN ('REP', 'MGR1')
ORDER BY s.mgr_2, s.mgr_1, rep;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
very nice indeed.
(but thinks) it would be prettier in 'comma first' layout :)
0
 

Author Closing Comment

by:MRPT
Comment Utility
Thank you very much.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now