Re: Embeded SQL Question

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