Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    13

    Unanswered: DB2 Insert Into w/ select w/ joins

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2004
    Posts
    13

    I love DB2

    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.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •