Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Unanswered: Help with Rollup

    I have the following snippet from a stored procedure. It is a period-to-date report showing store-level sales rolled up to district rolled up to company. It works fine except for the column the client program uses to sort by:

    Code:
    WITH  pivoted_foods AS (SELECT storeid,
                                  week_number,
                                  --== List wwkly fields in the order of presentation on report
                                  MAX(DECODE(trunc(to_date(week_date, 'dd-Mon-yy'), 'iy'), TRUNC(to_date(p_bop,'dd-Mon-yy'), 'iy'), landau_net_sales)) landau_net_sales,
                                  MAX(DECODE(trunc(to_date(week_date, 'dd-Mon-yy'), 'iy'), TRUNC(to_date(p_bop,'dd-Mon-yy'), 'iy'), aw_sales)) aw_sales,
                                  --==  List YTD  in corresponding order
                                  MAX(DECODE(trunc(to_date(week_date, 'dd-Mon-yy'), 'iy'), TRUNC(to_date(p_bop,'dd-Mon-yy'), 'iy'), lns_ytd)) lns_ytd,
                                  MAX(DECODE(trunc(to_date(week_date, 'dd-Mon-yy'), 'iy'), TRUNC(to_date(p_bop,'dd-Mon-yy'), 'iy'), aws_ytd)) aws_ytd,
                                     --==  List PTD  in corresponding order
                                  MAX(DECODE(trunc(to_date(week_date, 'dd-Mon-yy'), 'iy'), TRUNC(to_date(p_bop,'dd-Mon-yy'), 'iy'), lns_ptd)) lns_ptd,
                                  MAX(DECODE(trunc(to_date(week_date, 'dd-Mon-yy'), 'iy'), TRUNC(to_date(p_bop,'dd-Mon-yy'), 'iy'), aws_ptd)) aws_ptd,
                           FROM   foods
                           GROUP BY storeid,
                                    week_number),
          storedetails AS (SELECT companyname, districtname, storeid,  storename, storenbr FROM   kfc_report_stores 
                           WHERE  UPPER(storename) NOT LIKE  '%CLOSED%'
                           ORDER BY districtname, storenbr),
                 dummy AS (SELECT level col1  FROM   dual  CONNECT BY level <= 2)
    SELECT  STORES.COMPANYNAME,
            STORES.DISTRICTNAME,
            CASE 
                 WHEN DECODE(GROUPING(DISTRICTNAME),1,1,NULL) = 1 
                 THEN DISTRICTNAME
                 ELSE STORES.STORENBR
            END AS STORENBR,
            DECODE(DUMMY.COL1, 1, 'NET - LANDAU',
                               2, 'NET - AW'
                            ) CATEGORY,     
           SUM(CASE WHEN pf.week_number = 1 AND dummy.col1 = 1 THEN pf.landau_net_sales
                    WHEN pf.week_number = 1 AND dummy.col1 = 2 THEN pf.aw_sales
                    END) week1,
           SUM(CASE WHEN pf.week_number = 2 AND dummy.col1 = 1 THEN pf.landau_net_sales
                    WHEN pf.week_number = 2 AND dummy.col1 = 2 THEN pf.aw_sales
                    END) week2,
           SUM(CASE WHEN pf.week_number = 5 AND dummy.col1 = 1 THEN pf.lns_ptd
                    WHEN pf.week_number = 5 AND dummy.col1 = 2 THEN pf.aws_ptd
                    END) period_to_date,
           SUM(CASE WHEN pf.week_number = 5 AND dummy.col1 = 1 THEN pf.lns_ytd
                    WHEN pf.week_number = 5 AND dummy.col1 = 2 THEN pf.aws_ytd
                    END) year_to_date,
            stores.storename, 
              DECODE(DUMMY.COL1, 
                             1, 1,
                              2, 2,
                              3, 3,
                              4, 4,
                              5, 5,
                              6, 6,
                              7, 7,
                              8, 8,
                              9, 9) SORTBYCOL,FROM   pivoted_foods pf
           LEFT OUTER JOIN storedetails stores ON stores.storeid = pf.storeid,
           dummy
    WHERE NVL(stores.STOREID, 0) <> 0  
    group by decode
            ( dummy.col1
            , 1, 'NET - LANDAU'
            , 2, 'NET - AW'
            )        
          , stores.companyname
          , rollup
            ( stores.districtname
            , ( dummy.col1
              , stores.storenbr
              , stores.storename
            )
            )
    ORDER BY stores.COMPANYNAME,stores.DISTRICTNAME,STORES.STORENBR, DUMMY.COL1;
    My SortByCol field does is output as is should at the storelevel, but does not for the the district and company level. How do I achieve? The column is null in these cases.

    Entire SQL statement with data is attached.
    Attached Files Attached Files
    Last edited by row118; 11-06-09 at 19:57. Reason: Added attachment.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you can use NVL() to put an appropriate sort value into SORTORDER for district- and company-level records.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2009
    Posts
    6
    NVL Will assure the field is not blank but how would i use to get the correct sort order.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •