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

>From STRSQL this worked:

select * from rob/assessors
....+....1....+....2....+
  ASSESSOR_ID   A_NAME 
            1   sam 
********  End of data  **


INSERT INTO ROB/ASSESSORS SELECT max(assessor_id) + 1, 'frank' FROM 
rob/assessors


select * from rob/assessors
....+....1....+....2....+
  ASSESSOR_ID   A_NAME 
            1   sam 
            2   frank 
********  End of data  **

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

1)  No colon on assessor_id
2)  Ah, you'll figure it out...
Does require that there be at least one record in assessors.  Oh I suppose 
I could use a coalesce to test max(assessor_id).




Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com