Re: Embeded SQL Question

[Jeff Young <cooljeff913@xxxxxxxxx>]

Thanks Scott,
How is the ind field defined?

Thanks again,
 
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 
  
 






________________________________
From: Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Sent: Mon, May 24, 2010 4:10:31 PM
Subject: 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