Re: Embeded SQL Question

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

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