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!
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'"