Re: Embeded SQL Question

Hi Jeff,

You're missing the parenthesis around your SELECT. This is needed to do a 'fullselect' in an expression.

Try this:

Exec SQL
Set :Last_Start =
( Select Max(BbCCtT) From VoCTrack
Where BBAssign = :Assignment and
BBCode = 'STR'
Group by BbAssign
Having Count(*)> 1 );

The other thing you may want to think about is that this SELECT statement can very possibly result in a NULL. If there aren't any records where BBAssign = :Assignment, you'll get back a NULL which your statement doesn't even check for.

So IMHO it'd be better to do this:

Exec SQL
Set :Last_Start :Ind =
( Select Max(BbCCtT) From VoCTrack
Where BBAssign = :Assignment and
BBCode = 'STR'
Group by BbAssign
Having Count(*)> 1 );

And then verify that :Ind isn't set to -1 (indicating null) after running the statement.

Good luck

On 5/24/2010 2:11 PM, Jeff Young wrote:

Given the following SQL Statement, why is the precompiler complaining about the MAX keyword?

Exec SQL
Set :Last_Start =
Select Max(BbCCtT) From VoCTrack
Where BBAssign = :Assignment and
BBCode = 'STR'
Group by BbAssign
Having Count(*)> 1;

Thanks,

Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2
IBM Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3