Tags: cmt_csf_typ_cd, cmt_seq_nr, cmt_te, cmt_typ_cd, database, db2, insert, l_pri_key_vlu_te, mysql, oracle, query--insert, rec_ins_ts, rec_ins_usr, sql, statement, subquery, tbl_na, tuicmtb

Subquery in an insert statement

On Database » DB2

6,934 words with 12 Comments; publish: Wed, 05 Dec 2007 05:13:00 GMT; (25062.50, « »)

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

All Comments

Leave a comment...

  • 12 Comments
    • 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?

      #1; Tue, 11 Dec 2007 17:56:00 GMT
    • 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?)

      #2; Tue, 11 Dec 2007 17:57:00 GMT
    • 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...

      #3; Tue, 11 Dec 2007 17:58:00 GMT
    • 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

      #4; Tue, 11 Dec 2007 17:59:00 GMT
    • 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..
      #5; Tue, 11 Dec 2007 18:00:00 GMT
    • 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.

      #6; Tue, 11 Dec 2007 18:01:00 GMT
    • 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. :)

      #7; Tue, 11 Dec 2007 18:02:00 GMT
    • 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 :-)
      #8; Tue, 11 Dec 2007 18:03:00 GMT
    • 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

      #9; Tue, 11 Dec 2007 18:04:00 GMT
    • 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.

      #10; Tue, 11 Dec 2007 18:05:00 GMT
    • 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

      #11; Tue, 11 Dec 2007 18:06:00 GMT
    • 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

      #12; Tue, 11 Dec 2007 18:07:00 GMT