Friday, December 9, 2011

SQL0206N, SQL0180N & SQL0408N

I observed below mentioned error while working with DB2 query.

Error :
 S0022(-206)[IBM][CLI Driver][DB2/SUN64] SQL0206N  "2" is not valid in the context where it is used.  SQLSTATE=42703
 (0.58 secs)

Solution :
This error occurs when u try to compare OR assign values of different datatypes. For example COUNT = '2' will throw above error because COUNT is INT and you are trying to assing STRING to it. To avoid above error make sure datatypes are correct.

Error :

  22007(-180)[IBM][CLI Driver][DB2/SUN64] SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007
 (0.73 secs)

Solution :

This error occurs when working with DATETIME datatypes. Usually when assigning or comparing STRING to a DATETIME value.

If you need to compare / assign a STRING to DATETIME use below syntax.

DATETIME_VARIABLE = TIMESTAMP ('2011-12-08-15.49.26.000625')

Error :
  22005(-408)[IBM][CLI Driver][DB2/SUN64] SQL0408N  A value is not compatible with the data type of its assignment target.  Target name is "DATETIME_VARIABLE".  SQLSTATE=42821
 (0.48 secs)


To convert a character string to a date or time value, you can use:

TIMESTAMP ('2002-10-20 12:00:00')
DATE ('2002-10-20')
DATE ('10/20/2002')
TIME ('12:00:00')
TIME ('12.00.00')

The TIMESTAMP(), DATE() and TIME() functions accept many other formats.