Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Unanswered: inserts are getting me down :(

    Hiya everyone,

    I am kinda new to the db development environment and have got stuck on something. I am trying to copy a row from one of my tables and insert it at the bottom of the table. The SQL I am trying to use is as follows:


    insert into quotationitem (QUOTATIONNO, VERSION, ITEMNO, JOBITEMID, MODELID, THIRDDIV, CALTYPE, REQUIRE, CALPRICE, REPAIR, QUANTITY, USERID,LASTMODIFIED, DISCOUNT, BASECAL, BASEREPAIR, CURRENCY, BASECURRENCY, STATUS) select quotationno, version, MAX (itemno +1), jobitemid, modelid, thirddiv, caltype, require, calprice, repair, quantity, userid, lastmodified, discount, basecal, baserepair, currency, basecurrency, status from quotationitem where quotationno = '50001/02' and itemno = 4 and version = 1

    The primary key for the table is made up of quotationno, version and itemno but I want to keep all the data in the row the same apart from the itemno which I want to set to one higher than the existing highest itemno. (I think that makes sense!). However, when I execute the statement I get:

    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0119N An expression starting with "VERSION" specified in a SELECT clause,

    HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or

    it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column

    function and no GROUP BY clause is specified. SQLSTATE=42803


    I am pretty sure I am being a bit blond and doing something silly but I can't figure it out!! Any help or suggestions would make my Tuesday a lot easier!

    Cheers!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would issue the following statement by itself to make sure it works:

    select quotationno, version, MAX (itemno +1), jobitemid, modelid, thirddiv, caltype, require, calprice, repair, quantity, userid, lastmodified, discount, basecal, baserepair, currency, basecurrency, status from quotationitem where quotationno = '50001/02' and itemno = 4 and version = 1

    I am a little suspicious of MAX (itemno +1), but don't know for sure. You might also try MAX(itemno) +1 to see if that works.

  3. #3
    Join Date
    Jun 2003
    Posts
    2
    Yep, it is a problem with the MAX function, I tried this:

    select quotationno, version, itemno, jobitemid, modelid, thirddiv, caltype, require, calprice, repair, quantity, userid, lastmodified, discount, basecal, baserepair, currency, basecurrency, status from quotationitem where quotationno = '50001/02' and itemno = 4 and version = 1

    and it is fine. But when I add the max function it now gives this error:

    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0119N An expression starting with "VERSION" specified in a SELECT clause,

    HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or

    it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column

    function and no GROUP BY clause is specified. SQLSTATE=42803


    I shall have a tinker with it but I am a little lost. Any ideas anyone??

    Cheers

    Barb

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Use a select statement in place of max function ...

    The following statement is logically wrong ;-) , but it demonstates something you want to do ....

    db2 "insert into employee select '000341', firstnme, midinit, lastname, workdept ,phoneno,hiredate,job,(select distinct max(edlevel+1) from employee),sex,birthdate,salary,bonus,comm from employee where empno='000340'"


    Cheers

    Sathyaram

Posting Permissions

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