Problems with using DB2

1. Character connection problem of "|"

In DB2, "|" is used to connect strings, which is very different from other languages and databases using "+. When "|" is used, "[IBM] [CLI driver] [DB2/6000] sql0440n cannot be found. The type" function "is named" | "and is compatible. the authorization routine of the independent variable. Sqlstate = 42884 "error because a non-character type is involved in the" | "operation. If a variable or field appears in the" | "operation, the solution is to pass the variable or field to the char () function and then participate in the operation. The following is an example:

Select a. inf | '-' | A. IYF | '-01' from tablea-- An error occurs. inf year and IYF month.

Select char (A. inf) | '-' | char (A. IYF) | '-01' from tablea-- Correct

2. Time Comparison Problems

In DB2, The datediff function cannot be used, or I cannot use it. How can I compare the difference between the two times and return the value in the unit of day, month, or year? Use the timestampdiff () function! Example:

Select timestampdiff (64, char (timestamp (A. dcraetetime)-timestamp ('2017-5-25-13.56.41 ') from tablea

-- 64 indicates that the value in the unit of month is returned.

3. Like cannot be followed by a Field

If this is the case, an error occurs:

Select Item1 from Table1 where item2 like item3 | '%'

If you only want to write like as above, you can use the left or substr function:

Select Item1 from Table1 where left (item2, length (item3) = item3

Or

Select Item1 from Table1 where substr (item2, 1, length (item3) = item3