Nested SELECT in INSERT (was RE: Was Long SQL Field Names)

On Thu, 2004-08-19 at 19:46, Rick.Chevalier@xxxxxxxxxxxxxxx wrote:
> Joel,
> 
> You might try using an alias.  It's been a while since I had this issue but 
> IIRC that's how I solved it.  I don't have time right now to create a file to 
> test it, sorry.
> 
> HTH,
> 
> Rick

OK, further experimentation has shown that it is in fact a problem with
the SQL statement and the long names work fine.

I'm trying to essentially "auto-increment" in V5R1, so I had the
following for my INSERT statement:

c/exec sql
c+      insert into assessors
c+      values( select max( :assessor_id ) + 1 from assessors ,
c+                      :a_INITS,
c+                      :a_NAME,
c+                      :a_CHGUSER,
c+                      :a_CHGSTAMP )
c/end-exec

The compiler kept complaining that "assessor_id" is not in the table. 
When I broke the statement into two statements, it works:

c/exec sql
c+       select max( assessor_id ) + 1
c+         into :a_ASSESSOR
c+         from assessors
c/end-exec
c/exec sql
c+       insert into assessors
c+       values( :a_ASSESSOR ,
c+               :a_INITS,
c+               :a_NAME,
c+               :a_CHGUSER,
c+               :a_CHGSTAMP )
c/end-exec

So now I guess I have an SQL question... what is wrong in the first
statement?

Joel
http://www.rpgnext.com

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Joel Cochran
> Sent: Thursday, August 19, 2004 10:39 AM
> To: RPG programming on the AS400 / iSeries
> Subject: Long SQL Field Names
> 
> 
> I have an SQL created file with long field name.  It also has shorter
> system names for the fields.  I have an SQL statement in an RPG program
> that is trying to use the long field name, but I get a compile error
> saying the column is not in the table.
> 
> Here is the CREATE TABLE statement...
> 
> create table brmajobs.Assessors
> ( assessor_id for inspector int not null ,
>   assessor_initials for inits char(50) not null ,
>   assessor_name for name char(50) not null ,
>   last_change_user for chgUser char(10) not null ,
>   last_change_timestamp for chgStamp timestamp not null with default ,
>   Primary Key( assessor_id )
> );
> 
> And here is the DSPFFD for the first field:
> 
>  Field Level Information
>                Data        Field  Buffer    Buffer        Field   
> Column
>     Field      Type       Length  Length  Position        Usage   
> Heading
>     INSPECTOR  BINARY       9  0       4         1        Both    
> Assessor ID
>       Field text  . . . . . . . . . . . . . . . :  Assessor ID
>       Alternative name  . . . . . . . . . . . . :  ASSESSOR_ID
>       Default value . . . . . . . . . . . . . . :  None 
> 
> 
> I can use the field name "assessor_id" in STRSQL, but not in the
> SQLRPGLE module.  Is there a compiler option I have to set to use the
> long names or do I have to use the shorter system name in programs?
> 
> Joel
> http://www.rpgnext.com
> 
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.