Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: Need Help with Min Function

    Hey guys,

    I'm stuck on this query, and would appreciate any help. I want my query to search for two things:


    People with a voucher ID ending in D

    People whose first payment date in the anty_pymt table is 05-01-2010.


    This code will meet the second condition:

    I've tried this:

    Code:
    select * from dsnp.pr01_t_anty_pymt A
    inner join (select a.recip_ssn_nbr, min(a.anty_Pymt_Dt) as MinDate 
    from dsnp.pr01_t_anty_pymt A,
         dsnp.pr01_t_recip_sys B
    
    where b.RIGHT (VOUCHER_ID_CD,1) = 'D'
    
    group by a.recip_ssn_nbr) X 
    on A.recip_ssn_nbr = X.recip_ssn_nbr and 
    A.anty_pymt_dt = MinDate where A.anty_pymt_dt = '2010-05-01'

    DB2 doesn't like the voucher id part. The query runs if I take this out. I've tried placing it outside the derived table, but still doesn't work.


    Also, is there any way I can search for this criteria inside a case statement? Something like this:

    Code:
    sum(case When RIGHT (VOUCHER_ID_CD,1) = 'D' and MIN(B.ANTY_PYMT_DT) = '2010-05-01' then 1 end)   AS "DISABILITY RETIREES",

    DB2 doesn't like this code at all!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't like to post untested code, but try this:

    select *
    from dsnp.pr01_t_recip_sys B
    where RIGHT(B.VOUCHER_ID_CD,1) = 'D'
    and exists
    (select 1 from dsnp.pr01_t_anty_pymt A
    where A.recip_ssn_nbr = B.recip_ssn_nbr and '2010-05-01' =
    (select min(A.anty_pymt_dt) from dsnp.pr01_t_anty_pymt C where C.recip_ssn_nbr = A.recip_ssn_nbr)


    Edited to change function name per Lenny77 comments.
    Last edited by Marcus_A; 05-03-10 at 21:15.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question What do you want to get, using you query

    1. Yours:

    Code:
    select a.recip_ssn_nbr, min(a.anty_Pymt_Dt) as MinDate 
    from dsnp.pr01_t_anty_pymt A,
           dsnp.pr01_t_recip_sys   B
    
    where b.RIGHT (VOUCHER_ID_CD,1) = 'D'
    
    group by a.recip_ssn_nbr
    Returns:

    No function by the name "B.RIGHT" having compatible arguments was found in the function path
    2. Change to:

    Code:
    select a.recip_ssn_nbr, min(a.anty_Pymt_Dt) as MinDate 
    from dsnp.pr01_t_anty_pymt A,
          dsnp.pr01_t_recip_sys    B
    
    where RIGHT (b.VOUCHER_ID_CD,1) = 'D'
    
    group by a.recip_ssn_nbr
    3. Result could be Cartesian product, because there is nothing common between table "A" and table "B".
    Actually this query has no sense for me
    .

    Lenny

  4. #4
    Join Date
    Mar 2010
    Posts
    32
    Thanks for the help. I should have caught that b.right.

    Can someone explain to me why this query doesn't work? I get invalid function use error.


    SELECT recip_ssn_nbr
    from dsnp.pr01_t_anty_pymt
    where min(anty_pymt_dt) = '2010-05-01

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dvdaddict32 View Post
    Thanks for the help. I should have caught that b.right.

    Can someone explain to me why this query doesn't work? I get invalid function use error.


    SELECT recip_ssn_nbr
    from dsnp.pr01_t_anty_pymt
    where min(anty_pymt_dt) = '2010-05-01
    Did you read the explanation of the error message?

    An aggregate function or OLAP function can only be used in the select list of a fullselect, the having clause, or, with restrictions, in a WHERE clause or GROUP BY clause.

    A WHERE clause can contain an aggregate function or OLAP function only if that clause appears within a subquery of a HAVING clause and the argument of the function is a correlated reference to a group.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Db2

    Quote Originally Posted by dvdaddict32 View Post
    Thanks for the help. I should have caught that b.right.

    Can someone explain to me why this query doesn't work? I get invalid function use error.


    SELECT recip_ssn_nbr
    from dsnp.pr01_t_anty_pymt
    where min(anty_pymt_dt) = '2010-05-01
    If you suppose to use DB2 you have to know DB2.

    Code:
    SELECT recip_ssn_nbr
    from dsnp.pr01_t_anty_pymt
    Group By recip_ssn_nbr
    Having min(anty_pymt_dt) = '2010-05-01'
    Lenny

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's a basic SQL question, actually. MIN() with a single parameter is an aggregated function and it works on a set of rows. The WHERE clause only operates on a single row. Thus, it doesn't make any sense to try to use MIN() in the WHERE clause. You either need a subselect and the MIN() in the SELECT list, or you use HAVING as Lenny has shown.

    p.s: I recommend some SQL training courses. Finding the rows that have the min/max value is usually covered in those - aside from other topics.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Queries manipulation

    We can simply change our query from:

    Code:
    SELECT recip_ssn_nbr
    from dsnp.pr01_t_anty_pymt
    Group By recip_ssn_nbr
    Having min(anty_pymt_dt) = '2010-05-01'
    To query:

    Code:
    SELECT recip_ssn_nbr
    from dsnp.pr01_t_anty_pymt p1
    , table(
    select min(p3.anty_pymt_dt) from dsnp.pr01_t_anty_pymt p3
    where  p3.recip_ssn_nbr  = p1.recip_ssn_nbr
        and p3.anty_pymt_dt >= '2010-05-01'   ) p2
    where  p1.anty_pymt_dt  = p2.anty_pymt_dt  
        and p2.anty_pymt_dt  = '2010-05-01'
    But from point of view the second one is not better then first one.

    Lenny

Posting Permissions

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