Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Unhappy Unanswered: Subquery in an insert statement

    Hi,

    I have this query:
    --------------------------
    INSERT INTO TUICMTB(CMT_CSF_TYP_CD,CMT_TYP_CD,CMT_TE,TBL_NA,TB L_PRI_KEY_VLU_TE,CMT_SEQ_NR,REC_INS_TS,REC_INS_USR _NR,CMT_TE_SYS_NR)
    VALUES(2,6,'#CMT_TE#','TUIXCPB','#PKG_XCP_SYS_NR#' ,'1',#createODBCDateTime(now())#,'#cookie.usr_nr#' ,
    (
    SELECT MAX(cmt_te_sys_nr) + 1
    FROM TUICMTB
    )
    )
    -----------------------------

    ... and I get this error:
    -----------------------------
    DB2 Error Code = -104
    [IBM][CLI Driver][DB2] SQL0104N An unexpected token "MAX" was found following "". Expected tokens may include: "+ ) - ". SQLSTATE=42601
    -----------------------------

    All I'm trying to do is insert a new record into a table, and at the same time calculate the max number in a column and add 1 to it... because I need to increment the number by 1 for each new record inserted.
    Any ideas why I'm getting the error?

    Thanks,

    NB
    Last edited by nbozic; 04-29-04 at 15:23.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't think you can use a subselect within VALUES. Besides, after your table has grown to few hundred thousand rows you'll get a performance nightmare.

    Did you consider using an identity field? Or a sequence?

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by n_i
    I don't think you can use a subselect within VALUES. Besides, after your table has grown to few hundred thousand rows you'll get a performance nightmare.

    Did you consider using an identity field? Or a sequence?
    I just tried it, it works (although I agree with you, it's not a good way to do this!)

    create table maxtab (a integer);
    insert into maxtab values (1);
    insert into maxtab values (select max(a)+1 from maxtab);

    I think the original poster's problem is something with quoting those #blah# sections? (What are those, anyway?)
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by J Petruk

    I think the original poster's problem is something with quoting those #blah# sections? (What are those, anyway?)
    These look like pseudo-variables that are substituted by some kind of pre-processor... I would try enclosing '#createODBCDateTime(now())#' into single quotes - db2 expects a date value in quotes...

  5. #5
    Join Date
    Mar 2004
    Posts
    51
    yes, everything between # signs are variables that I want to output... I use ColdFusion scripting. The # signs should not be a problem, nor the Date field (#createODBCDateTime(now())#) without the quotes.
    The query stops and throws the error only when it reaches the sub query - there seems to be some kind of problem with it... If you got your subquery to work, I'm not sure how you did it because mine just won't do it.

    NB

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Can you tell ColdFusion to print the statement after the variables have been substituted? It would help to see the actual statement that's being processed..

  7. #7
    Join Date
    Mar 2004
    Posts
    51
    The variables are dynamic and may look like a potential problem, but they are not the issue. Let's say I run this query:
    ---------------------------
    INSERT INTO TUICMTB(CMT_CSF_TYP_CD,CMT_TYP_CD,CMT_TE,TBL_NA,TB L_PRI_KEY_VLU_TE,CMT_SEQ_NR,REC_INS_TS,REC_INS_USR _NR,CMT_TE_SYS_NR)
    VALUES(2,6,'#CMT_TE#','TUIXCPB','#PKG_XCP_SYS_NR#' ,'1',#createODBCDateTime(now())#,'#cookie.usr_nr#' ,
    (
    12 + 1
    )
    )
    ---------------------------
    The query above will execute without any problems - only when the query contains the subquery, then I get the error. When I run the subquery stand-alone, it also works fine.

    I have no idea what the problem is.
    Last edited by nbozic; 04-30-04 at 10:43.

  8. #8
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by nbozic
    The variables are dynamic and may look like a potential problem, but they are not the issue. Let's say I run this query:
    ---------------------------
    INSERT INTO TUICMTB(CMT_CSF_TYP_CD,CMT_TYP_CD,CMT_TE,TBL_NA,TB L_PRI_KEY_VLU_TE,CMT_SEQ_NR,REC_INS_TS,REC_INS_USR _NR,CMT_TE_SYS_NR)
    VALUES(2,6,'#CMT_TE#','TUIXCPB','#PKG_XCP_SYS_NR#' ,'1',#createODBCDateTime(now())#,'#cookie.usr_nr#' ,
    (
    12 + 1
    )
    )
    ---------------------------
    The query above will execute without any problems - only when the query contains the subquery, then I get the error. When I run the subquery stand-alone, it also works fine.

    I have no idea what the problem is.
    What version are you running? Maybe this is something that's changed between 7 and 8? I don't think so, but I don't have an older instance to test on.

    This one's bugging me.
    --
    Jonathan Petruk
    DB2 Database Consultant

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I've tried your example on 7.2 fp5 and it worked. I still suspect there may be something wrong with the way the statement comes out of the pre-processor... but Mr. (or Ms. ) Bozic refuses to cooperate in providing us with the final SQL string :-)

  10. #10
    Join Date
    Mar 2004
    Posts
    51
    I think we're running DB2 version 7.
    I've successfuly completed several nested DB2 queries before. Some I could not get to work, for some reason...
    Could it be that I'm using wrong syntax in some way (although it doesn't seem probable), in the current query and the queries I tried before?

    NB

  11. #11
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by nbozic
    I think we're running DB2 version 7.
    I've successfuly completed several nested DB2 queries before. Some I could not get to work, for some reason...
    Could it be that I'm using wrong syntax in some way (although it doesn't seem probable), in the current query and the queries I tried before?

    NB
    I tried a test... with your query and a faked table:

    CREATE TABLE TUICMTB (
    CMT_CSF_TYP_CD INTEGER,
    CMT_TYP_CD INTEGER,
    CMT_TE VARCHAR(20),
    TBL_NA VARCHAR(20),
    TBL_PRI_KEY_VLU_TE VARCHAR(20),
    CMT_SEQ_NR varchar(20),
    REC_INS_TS varchar(20),
    REC_INS_USR_NR varchar(20),
    CMT_TE_SYS_NR INTEGER);

    INSERT INTO TUICMTB
    (CMT_CSF_TYP_CD,
    CMT_TYP_CD,
    CMT_TE,TBL_NA,
    TBL_PRI_KEY_VLU_TE,
    CMT_SEQ_NR,
    REC_INS_TS,
    REC_INS_USR_NR,
    CMT_TE_SYS_NR)
    VALUES(2,6,'#CMT_TE#','TUIXCPB','#PKG_XCP_SYS_NR#' ,'1','BLAH','#cookie.usr_nr#',
    (
    SELECT MAX(cmt_te_sys_nr) + 1
    FROM TUICMTB
    )
    );

    Worked. Your query is fine, not sure what it is.
    --
    Jonathan Petruk
    DB2 Database Consultant

  12. #12
    Join Date
    Mar 2004
    Posts
    51
    This is insane... I'm surprised that you got it to work, especially since it's exactly the same query compared to the one I run. There are a couple more things I will try, ColdFusion side, before I give up... It just doesn't make sense....
    (Right now I'm unable to provide the final SQL string, because yesterday we emptied all data from all tables in the database and will re-populate everything in a few days. Basically there is no data for the query to run, as of now... )

    Anyway, thanks for checking out the problem

    NB
    Last edited by nbozic; 04-30-04 at 12:17.

  13. #13
    Join Date
    Mar 2004
    Posts
    448
    I did the same that Jonathan did, it worked.
    I ran it on the linux box, just when I cut and paste it introduces some gap in the column name, I rectified it,nothing extraordinary


    regards.
    mujeeb

Posting Permissions

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