Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: Using Max() function

    The above below returns three rows.

    SELECT J01_REGION, I20_MMS_NO, I20_CONSUMER_ID, I21_CONSUMER_ID, I21_CASE_ID, I42_FIN_MGR_PROV, J01_PROVIDER, I21_CASE_OPEN_DT, I42_FM_BEG_DT
    FROM SERDB.I20_CONSUMER_T INNER JOIN SERDB.I21_CONS_CASE_T ON I20_CONSUMER_ID = I21_CONSUMER_ID
    INNER JOIN SERDB.I42_FIN_MGR_T ON I21_CASE_ID = I42_CASE_ID
    INNER JOIN SERDB.J01_PROVIDER_T ON I42_FIN_MGR_PROV = J01_PROVIDER
    WHERE J01_REGION = 2
    AND I20_CONSUMER_ID = 96603;

    ***

    What I would like is for the query to return one row by using the MAX values on I21_CASE_OPEN_DT & I42_FM_BEG_DT.

    SELECT J01_REGION, I20_MMS_NO, I20_CONSUMER_ID, I21_CONSUMER_ID, I21_CASE_ID, MAX(I21_CASE_OPEN_DT), MAX(I42_FM_BEG_DT), I42_FIN_MGR_PROV, J01_PROVIDER
    FROM SERDB.I20_CONSUMER_T INNER JOIN SERDB.I21_CONS_CASE_T ON I20_CONSUMER_ID = I21_CONSUMER_ID
    INNER JOIN SERDB.I42_FIN_MGR_T ON I21_CASE_ID = I42_CASE_ID
    INNER JOIN SERDB.J01_PROVIDER_T ON I42_FIN_MGR_PROV = J01_PROVIDER
    WHERE J01_REGION = 2
    AND I20_CONSUMER_ID = 96603
    GROUP BY I21_CASE_OPEN_DT, I42_FM_BEG_DT;

    Error: SQL0122N A SELECT statement with no GROUP BY clause contains a column name or expression and a column function in the SELECT clause, or a column name or expression is contained in the SELECT clause but not in the GROUP BY clause. SQLSTATE=42803


    Does anyone have a suggestion as to what else I could use?

    Thanks
    Last edited by citi; 08-22-12 at 14:16.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by citi View Post
    Error: SQL0122N A SELECT statement with no GROUP BY clause contains a column name or expression and a column function in the SELECT clause, or a column name or expression is contained in the SELECT clause but not in the GROUP BY clause. SQLSTATE=42803

    The error message tells you the problem. All none aggregated columns in the SELECT need to be in the GROUP BY.

    Andy

  4. #4
    Join Date
    Aug 2007
    Posts
    56
    Thanks. I figured out how to use the GROUP BY without getting junk back.

Posting Permissions

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