Need Help with Min Function

dvdaddict32 · #1 (**permalink**) 05-03-10, 16:24

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!

Marcus_A · #2 (**permalink**) 05-03-10, 17:16

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.

Lenny77 · #3 (**permalink**) 05-03-10, 18:17

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:

Quote:

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

dvdaddict32 · #4 (**permalink**) 05-04-10, 13:29

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

Marcus_A · #5 (**permalink**) 05-04-10, 14:30

Quote:

Originally Posted by dvdaddict32

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.

Lenny77 · #6 (**permalink**) 05-04-10, 17:09

Quote:

Originally Posted by dvdaddict32

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

stolze · #7 (**permalink**) 05-07-10, 02:46

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.

Lenny77 · #8 (**permalink**) 05-07-10, 10:56

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

Internet Services

Web Development

Digital Marketing

Consumer Tech