locked
How to incorporate another table in view with having sum? RRS feed

  • Question

  • Hi Folks -

    I have a SQL environment that is the SOURCE for and OAC application (Oracle Analytics in the Cloud).  I have a report that pulls the Actual and Budget figures for a given Organization -> Department -> Calendar Day.

    My view is as follows:

    SELECT SAL.MRP_ORGANIZATION_KEY,
        SAL.MRP_CALENDAR_KEY,
        SAL.MRP_DEPARTMENT_KEY
    FROM F_DEPARTMENT SAL
    GROUP BY SAL.MRP_ORGANIZATION_KEY,
        SAL.MRP_CALENDAR_KEY,
        SAL.MRP_DEPARTMENT_KEY
    HAVING SUM(SAL.SALES_RETAIL) <> 0

    As you can see, very simple.  I have another table (Dimension table) where we manage the Department Keys, Department Names and Department Category.  In the past, department key key used to make up its own department. However recently, we broke out each department and added them to a CATEGORY (for a more granular level of detail).

    Now let's take MRP_DEPARTMENT_KEY 3 & DEPT_CATEGORY 'Grocery' for an example.

    I'm running into a situation where I have budget in a department_key but NO sales...Therefore, my VIEW is excluding that row and thus, skewing my budget figures.  Here is a chart to show you:

    As you can see above, my Budget report in OAC is missing the 789 since it was excluded due to view logic.  What I would like to do is amend the view to leverage DEPT_CATEGORY as well so I can can capture the exception I mentioned above.

    I tried this but it doesn't seem to pull in the exception above. Am I doing it right?

    SELECT DISTINCT SAL.MRP_ORGANIZATION_KEY,
        SAL.MRP_CALENDAR_KEY,
        SAL.MRP_DEPARTMENT_KEY,
        CAT.DEPT_CATEGORY
    FROM F_DEPARTMENT SAL
    INNER JOIN D_DEPARTMENT CAT ON SAL.MRP_DEPARTMENT_KEY = CAT.MRP_DEPARTMENT_KEY
    GROUP BY SAL.MRP_ORGANIZATION_KEY,
        SAL.MRP_CALENDAR_KEY,
        SAL.MRP_DEPARTMENT_KEY,
        CAT.DEPT_CATEGORY
    HAVING SUM(SAL.SALES_RETAIL) <> 0

    ANy help is greatly appreciated. Thank you!!!!

    Thursday, December 26, 2019 4:10 PM

All replies

  • I'm not sure that I understand this, but if you have sales and budget in different tables, don't you need to check both?

    What appears logical to me is

    SELECT MRP_ORGANIZATION_KEY, MRP_CALENDAR_KEY, MRP_DEPARTMENT_KEY
    FROM   SELECT SAL.MRP_ORGANIZATION_KEY,
                  SAL.MRP_CALENDAR_KEY,
                  SAL.MRP_DEPARTMENT_KEY
                  SAL.SALES_RETAIL
           FROM   F_DEPARTMENT SAL
           UNION ALL
           SELECT BUD.MRP_ORGANIZATION_KEY,
                  BUD.MRP_CALENDAR_KEY,
                  BUD.MRP_DEPARTMENT_KEY
                  BUD.BUDGET_AMOUNT
           FROM   BUDGET BUD) AS u
    GROUP BY u.MRP_ORGANIZATION_KEY, uy.MRP_CALENDAR_KEY, u.MRP_DEPARTMENT_KEY
    HAVING SUM(u.SALES_RETAIL) <> 0 OR SUM(u.BUDGET_RETAIL) <> 0


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 26, 2019 6:54 PM
  • Thank you, I will give that a try.  The reason I wanted to use one SELECT is to ensure I captured both the sales and budget correct. But this UNION ALL should work.

    I am running into one issue, the second part of the HAVING SUM is throwing an error.

    SELECT 
        MRP_ORGANIZATION_KEY, MRP_CALENDAR_KEY, MRP_DEPARTMENT_KEY
    FROM 
        (
            SELECT 
                SAL.MRP_ORGANIZATION_KEY, SAL.MRP_CALENDAR_KEY, SAL.MRP_DEPARTMENT_KEY,
                SAL.SALES_RETAIL
            FROM 
                F_DEPARTMENT SAL
            UNION ALL
            SELECT 
                BUD.MRP_ORGANIZATION_KEY, BUD.MRP_CALENDAR_KEY, BUD.MRP_DEPARTMENT_KEY,
                BUD.SALES_BUDGET
            FROM 
                F_BUDGET BUD 
        ) u
    GROUP BY u.MRP_ORGANIZATION_KEY, u.MRP_CALENDAR_KEY, u.MRP_DEPARTMENT_KEY
    HAVING SUM(u.SALES_RETAIL) <> 0 OR SUM(u.SALES_BUDGET) <> 0
    ORA-00904: "U"."SALES_BUDGET": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    Error at Line: 30 Column: 41

    SALES_BUDGET is correct and it works fine if I just highlight the inner UNION ALL and run that. ANy ideas?

    Thursday, December 26, 2019 7:26 PM
  • ORA-00904: "U"."SALES_BUDGET": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    *Action:
    Error at Line: 30 Column: 41

    SALES_BUDGET is correct and it works fine if I just highlight the inner UNION ALL and run that. ANy ideas?

    Wait, this is a forum for using Transact-SQL on SQL Server, and you post an error that is coming from Oracle. So if you are using Oracle, you should look for an Oracle forum.

    That said, I realise that the query I posted was incorrect. This query should work:

    SELECT     MRP_ORGANIZATION_KEY, MRP_CALENDAR_KEY, MRP_DEPARTMENT_KEY
    FROM     (
            SELECT             SAL.MRP_ORGANIZATION_KEY, SAL.MRP_CALENDAR_KEY, SAL.MRP_DEPARTMENT_KEY,
                SAL.SALES_RETAIL, 0 AS SALES_BUDGET
            FROM             F_DEPARTMENT SAL
            UNION ALL
            SELECT             BUD.MRP_ORGANIZATION_KEY, BUD.MRP_CALENDAR_KEY, BUD.MRP_DEPARTMENT_KEY,
                0, BUD.SALES_BUDGET
            FROM             F_BUDGET BUD     ) u
    GROUP BY u.MRP_ORGANIZATION_KEY, u.MRP_CALENDAR_KEY, u.MRP_DEPARTMENT_KEY
    HAVING SUM(u.SALES_RETAIL) <> 0 OR SUM(u.SALES_BUDGET) <> 0

    Since this is all standard SQL, this query should run on Oracle too.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 26, 2019 10:25 PM
  • Thank you so much! That executes, just validating now.

    Can you explain to me what this does? 

    0, BUD.SALES_BUDGET

    Thank you!

    Friday, December 27, 2019 1:55 AM
  • Hi cdtakacs1, 

    Thank you for your issue . I agree with Erland Sommarskog's advice .

    Per your description , 'ORA-00904' this error might be related to Oracle . Please check if your are using SQL Server. If not , please post your issue in the corresponding  forum. Oracle Forum   Oracle and SQL Sever are all use SQL . They looks same but different actually in some feature functions or syntax. 

    Sorry that I'm not familiar with Oracle. But I find an article about ORA-00904. Hope it will help you. How to Resolve ORA-00904 Invalid Identifier

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 27, 2019 3:01 AM
  • Can you explain to me what this does? 

    0, BUD.SALES_BUDGET

    It returns a 0 for the SALES_RETAIL column. It could also be written as

    0 AS SALES_RETAIL, BUD.SALES_BUDGET

    for more clarity, but in a UNION query, the column names are taken from the first query.

    The key was that we needed two separate columns for the two amounts.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 27, 2019 7:53 AM
  • I am noticing this query is taking a pretty long time. Would there be any areas of improvement I could do here? The tables in which this is pulling from are indexed.

    Are you running this on SQL Server or Oracle? If you are running this on Oracle, you really need to ask in an Oracle forum, since what may be good advice on SQL Server may be completely off-target for Oracle.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 27, 2019 11:42 AM
  • Hi -

    I'm sorry, I'm running this on MSSQL. I was testing on Oracle by accident. We have switched over to MSSQL. Thank you.

    Friday, December 27, 2019 12:24 PM
  • I am noticing this query is taking a pretty long time. Would there be any areas of improvement I could do here? The tables in which this is pulling from are indexed.

    It seems to be that you don't need the joins to those dimension tables, but you can simplify the query to:

     SELECT
         TBL1.MRP_ORGANIZATION_KEY,
         TBL1.MRP_CALENDAR_KEY,
         TBL1.MRP_DEPARTMENT_KEY
     FROM
       (
         SELECT          FCT.MRP_CALENDAR_KEY,
             FCT.MRP_ORGANIZATION_KEY,
             FCT.MRP_DEPARTMENT_KEY,
             FCT.SALES_RETAIL

         FROM          F_DEPARTMENT FCT

       ) TBL1
       INNER JOIN
       (
         SELECT          FCT.MRP_CALENDAR_KEY,
             FCT.MRP_ORGANIZATION_KEY,
             FCT.MRP_DEPARTMENT_KEY,
             FCT.SALES_BUDGET

         FROM          F_BUDGET FCT
       ) TBL2 ON TBL1.MRP_ORGANIZATION_KEY = TBL2.MRP_ORGANIZATION_KEY              AND TBL1.MRP_DEPARTMENT_KEY = TBL2.MRP_DEPARTMENT_KEY              AND TBL1.MRP_CALENDAR_KEY = TBL2.MRP_CALENDAR_KEY
     GROUP BY      TBL1.MRP_ORGANIZATION_KEY,      TBL1.MRP_CALENDAR_KEY,      TBL1.MRP_DEPARTMENT_KEY

     HAVING      SUM(TBL1.SALES_RETAIL) <> 0 OR      SUM(TBL2.SALES_BUDGET) <> 0


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 27, 2019 10:30 PM