Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2013
    Posts
    15

    Unanswered: Need help with joining two grouped sub queries to my grouped main query

    Just a quick mockup of what I am looking for.

    Acct_Num Cust_Name B5DTL B5CTL
    12345678 Bob Builder 74.74 -408.02
    87654321 Joe Dirt 15.43 -1301.35

    When I try to join my sub queries in I get errors like the following.
    [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0122 - Column B5DTL or expression in SELECT list not valid.

    I have tried changing several things from names, to trying different columns... so I gather that I'm approaching it wrong.

    Using
    Windows 7
    TOAD 3.1


    I want these specific customers only.

    SELECT FBF800.ADCUST
    , ( BCCDCD.CDCD_FIRST || ' ' ) || BCCDCD.CDCD_LAST AS TCHR_NAME
    FROM
    ARD.FBF800 FBF800
    INNER JOIN
    CDATA.BCCDCD BCCDCD
    ON (FBF800.ADCUST = BCCDCD.CDCD_CUSTOMER_NUMBER)
    WHERE ((FBF800.ADCUST NOT IN ('076832211') AND FBF800.ADRSN IN ('BT', 'B4', 'B5')) AND FBF800.ADTRD BETWEEN 20120801 AND 20130731)
    GROUP BY FBF800.ADCUST
    , ( BCCDCD.CDCD_FIRST || ' ' ) || BCCDCD.CDCD_LAST


    I wanted to use the following sub queries and join the two columns B5DTL and B5CTL to get the mock up at the top.

    I grouped this so I would only get the summed debits per customer in this query from that specific date range.

    SELECT B5D.ADCUST
    , B5D.ADRSN
    , SUM(B5D.ADADJA) AS B5DTL
    FROM ARD.FBF800 B5D
    WHERE (B5D.ADRSN = 'B5')
    AND (B5D.ADADJA > 0)
    AND (B5D.ADTRD BETWEEN 20120801 AND 20130731)
    GROUP BY B5D.ADCUST
    , B5D.ADRSN

    I grouped this so I would only get the summed credits per customer in this query from that specific date range.

    SELECT B5C.ADCUST
    , B5C.ADRSN
    , SUM(B5D.ADADJA) AS B5CTL
    FROM ARD.FBF800 B5C
    WHERE (B5C.ADRSN = 'B5')
    AND (B5C.ADADJA < 0)
    AND (B5C.ADTRD BETWEEN 20120801 AND 20130731)
    GROUP BY B5C.ADCUST
    , B5C.ADRSN

    Any help would be greatly appreciated.
    Last edited by dotsofcolor; 06-19-13 at 17:40.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT B5C.ADCUST
    , B5C.ADRSN
    , SUM(B5D.ADADJA) AS B5CTL
    FROM ARD.FBF800 B5C
    WHERE (B5C.ADRSN = 'B5')
    AND (B5C.ADADJA < 0)
    AND (B5C.ADTRD BETWEEN 20120801 AND 20130731)
    GROUP BY B5C.ADCUST
    , B5C.ADRSN
    At least, SUM(B5D.ADADJA) must be wrong. It should be SUM(B5C.ADADJA).

  3. #3
    Join Date
    Jun 2013
    Posts
    15
    Oops sorry, that was a typo. I am currently using this as the third query which has all the correct names.

    SELECT B5C.ADCUST
    , B5C.ADRSN
    , SUM(B5C.ADADJA) AS B5CTL
    FROM ARD.FBF800 B5C
    WHERE (B5C.ADRSN = 'B5')
    AND (B5C.ADADJA < 0)
    AND (B5C.ADTRD BETWEEN 20120801 AND 20130731)
    GROUP BY B5C.ADCUST
    , B5C.ADRSN

    All the queries run fine independently but not when I join them.
    Last edited by dotsofcolor; 06-20-13 at 10:21.

  4. #4
    Join Date
    Jun 2013
    Posts
    15
    I was wondering if it was having an issue because I'm using an aggregate function and doing a GROUP BY and then trying to pull it into another query that I was also doing a GROUP BY and aggregate on also.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT B5D.ADCUST
    , B5D.ADRSN
    , SUM(B5D.ADADJA) AS B5DTL
    FROM ARD.FBF800 B5D
    WHERE (B5D.ADRSN = 'B5')
    AND (B5D.ADADJA > 0)
    AND (B5D.ADTRD BETWEEN 20120801 AND 20130731)
    GROUP BY B5D.ADCUST
    , B5D.ADRSN

    SELECT B5C.ADCUST
    , B5C.ADRSN
    , SUM(B5C.ADADJA) AS B5CTL
    FROM ARD.FBF800 B5C
    WHERE (B5C.ADRSN = 'B5')
    AND (B5C.ADADJA < 0)
    AND (B5C.ADTRD BETWEEN 20120801 AND 20130731)
    GROUP BY B5C.ADCUST
    , B5C.ADRSN

    Those two queries might be combined into one query.
    Code:
    SELECT B5.ADCUST
         , B5.ADRSN
         , SUM( CASE
                WHEN B5.ADADJA > 0 THEN
                     B5.ADADJA
                END  ) AS B5DTL
         , SUM( CASE
                WHEN B5.ADADJA < 0 THEN
                     B5.ADADJA
                END  ) AS B5CTL
     FROM  ARD.FBF800 B5
     WHERE B5.ADRSN = 'B5'
       AND B5.ADTRD BETWEEN 20120801 AND 20130731
     GROUP BY
           B5.ADCUST
         , B5.ADRSN

  6. #6
    Join Date
    Jun 2013
    Posts
    15
    Unfortunately, they have to remain as separate columns so I can keep the debits and credits separate.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What result do you want?

    Didn't you want like this?
    Acct_Num Cust_Name B5DTL B5CTL
    12345678 Bob Builder 74.74 -408.02
    87654321 Joe Dirt 15.43 -1301.35

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I felt that your queries might be not consistent with each other and with the result what you are looking for.

    For example:
    (1) First query had ADRSN IN ('BT', 'B4', 'B5'). Second and third queries had ADRSN = 'B5'.
    Did that mean you don't want to add ADADJA into SUM(ADADJA) for ADRSN IN ('BT', 'B4')?

    (2) First query had ADCUST NOT IN ('076832211'). But, second and third queries had not predicate for ADCUST.
    Is the value of ( BCCDCD.CDCD_FIRST || ' ' ) || BCCDCD.CDCD_LAST unneccesary for ADCUST = '076832211'?

    (3) Column names Acct_Num and Cust_Name in what you are looking for are not appear in your queries.
    (I could suppose ADCUST and TCHR_NAME might be corresponded to them. But, not sure!)

    (4) Second and third queries had ADRSN in GROUP BY item. But, it was not in what you are looking for.


    Anyway,
    this example might be worth to try...
    (though you may want to modify some predicates.)
    Code:
    SELECT f.ADCUST AS Acct_Num
         , (SELECT CDCD_FIRST || ' ' || CDCD_LAST
             FROM  CDATA.BCCDCD
             WHERE CDCD_CUSTOMER_NUMBER = f.ADCUST
           ) AS Cust_Name
         , SUM( CASE
                WHEN f.ADRSN  = 'B5'
                 AND f.ADADJA > 0   THEN
                     f.ADADJA
                END ) AS B5DTL
         , SUM( CASE
                WHEN f.ADRSN  = 'B5'
                 AND f.ADADJA < 0   THEN
                     f.ADADJA
                END ) AS B5CTL
     FROM  ARD.FBF800 AS f
     WHERE f.ADCUST NOT IN ('076832211')
       AND f.ADRSN IN ('BT', 'B4', 'B5')
       AND f.ADTRD BETWEEN 20120801 AND 20130731
     GROUP BY
           f.ADCUST

  9. #9
    Join Date
    Jun 2013
    Posts
    15
    Thanks for the feedback Tonkuma. My apologies for any confusion. I think that I need to provide what each query is grabbing and how it is being grouped/aggregated so that you can see how I would get that end result that is also grouped/aggrgated again. I started working up an Excel sheet yesterday with more detail and some sample data but might take me a few days to get it all together and posted while I complete other tasks. Hope that once I provide that it will be easier to work out a solution. I really appreciate your responses.

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
  •