I'm trying to create a DB2 SQL stored proc that does an insert into like so...

insert into hh.mytbl (field1, field2)
select field1, field2
from hh.mytbl2
where somefield = 0

this syntax works fine, but as soon as I try to join tables in the from clause of the select criteria, the proc won't compile.

While I know that I could move the join criteria to the where clause I don't want to do that if I shouldn't have to (because of outer joins and such). And yes, the select statement works fine if I pull it out and run just that.

If someone can please help me get the following type of statement to work inside of a proc I would greatly appreciate it...

insert into hh.mytbl (field1, field2)
select t1.field1, t2.field2
from hh.mytbl2 t1
inner join hh.mytbl3 t2
on t1.field1 = t2.field2
where t1.somefield = 0

I don't know why it doesn't work in the SP, but since inner join is the normal behavior if you just specify the join predicates in the where clause, why not just do that?

If you have a support contract with IBM, you should open a PMR.

Found out the problem had nothing to do w/ the insert statement. I had comments like so in the proc...

/****************
some comment
****************/

when I changed them to the style below, things worked fine...

/* ***************
some comment
*************** */

... even though I just finished writing about 20 procs that compliled just fine w/ the first style of comment.

I use -- to indicate comments on SQL procedures (the same as in an SQL Script). That is what IBM uses on their sample SP's.

I guess /* works because the SQL Procedures are converted to C programs.