db2

Equivalent of LIMIT for DB2

by

Using FETCH FIRST [n] ROWS ONLY: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.perf/db2z_fetchfirstnrows.htm SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY FROM EMP ORDER BY SALARY DESC FETCH FIRST 20 ROWS ONLY; To get ranges, you’d have to use ROW_NUMBER() (since v5r4) and use that within the WHERE clause: (stolen from here: http://www.justskins.com/forums/db2-select-how-to-123209.html) SELECT code, name, address FROM ( SELECT row_number() OVER ( ORDER … Read more

An unexpected token “CREATE TRIGGER

by

You have 2 things going on here – 1) When the “;” character is part of the SQL statement, it’s necessary to use a different character to terminate the statement. I typically use “@”. To tell the “db2” command that you have chosen a different character, use db2 -td@ or if you want to read … Read more

SQL update from one Table to another based on a ID match IN db2

by

DB2 does indeed support joins in an UPDATE statement, only not the way you think — DB2 follows the SQL ANSI standard: UPDATE Sales_Import SI SET Sales_Import.AccountNumber = ( SELECT RAN.AccountNumber FROM RetrieveAccountNumber RAN WHERE SI.LeadID = RAN.LeadID ) The above assumes that LeadID uniquely identifies records in RetrieveAccountNumber, otherwise you will get an error … Read more

tech