Problem with aggregate

[Manneke]

Hi,

I get an error message (cannot have aggregate function in where clause MA_V.ABDAT = MAX(MA_A.ABDAT) AND MA_V.MAN = MA_A.MAN) when I try to execute a query created in MS_SQL.
I've tried to modify the query but always got an error. Maybe is it too simple but I don't see the solution.
Here is the query working fine in MS_SQL:
SELECT (SELECT MA_V.VERT_F
FROM MA_VUNGEN MA_V
WHERE MA_V.ABDAT = MAX(MA_A.ABDAT) AND MA_V.MAN = MA_A.MAN) AS Contrat
FROM MA_VUNGEN AS MA_A
WHERE (MAN = '85557732') AND (ABDAT <= 5103)
GROUP BY MAN;
The purpose of this query is to retrieve the last valid Number according to a date.
ABDAT is a date in numeric format
VERT_F is a number of contract
MAN is the employee number

Any help will be appreciated

Thans for your time.

 

[jebry]

Hi!

Use a subquery:

MA_V.ABDAT IN (Select MAX(MA_A.ABDAT) From MA_VUNGEN)

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Manneke]

Hi Jebry,
I tried your solution but didn't get the expecting result.
Here is the query now:
SELECT (SELECT MA_V.VERT_F
FROM MA_VUNGEN MA_V
WHERE MA_V.ABDAT IN (SELECT MAX(MA_A.ABDAT) FROM MA_VUNGEN MA_A) AS Contrat
FROM MA_VUNGEN MA_A
WHERE (MAN = '85557732') AND (ABDAT <= 5143)
GROUP BY MAN;
Here is an example:
'85557732' 5082 191
'85557732' 5117 123
'85557732' 5144 191
'85557732' 5204 123

The result has to be 123. for the moment I always get 191 whatever the ABDAT I try.
Did I made something wrong ?

 

[lespaul]

try:

SELECT *
FROM MA_VUNGEN M
INNER JOIN (SELECT MAN, MAX(ABDAT) FROM MA_VUNGEN GROUP BY MAN) MA ON M.MAN = MA.MAN
WHERE MAN = '85557732' AND ABDAT < 5143


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Manneke]

Hi Lespaul,

If I try your query I get all the records that are before the defined date. That's not what I'm looking for; I just need the last closest to the date.

Thanks anyway

 

[Manneke]

Hi,

I've found something different and easier. Here is the query:
SELECT Last(VERT_F) As Contrat
FROM MA_VUNGEN
WHERE (MAN = '85557732') AND (ABDAT <= 5143)

With this one I always get the last contrat.

Thanks for your time guys, it helps me to find another way.

Nice WE