SQL应用报错800.Corresponding types must be compatible in CASE expression.
错误描述:
11:00:51 [SELECT - 0 row(s), 0.000 secs] [Error Code: -800, SQL State: IX000] Corresponding types must be compatible in CASE expression.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
IBM解释:
Cause
The data types of the THEN and ELSE part are different. For example the THEN part return a date value and the ELSE part return a date/time value.
Environment
Windows
Diagnosing the problem
Check your report for IF/THEN/ELSE expressions.
Resolving the problem
Verify your Impromptu report for IF/THEN/ELSE expressions.
Check the data type of the THEN part with the data type of the ELSE part. Both need to be return the same data type. If not you need to add an additional calculation or a function to transform the result into a result with the required data type.
Exapmle:
IF ( column <> 1 ) THEN ( date ) ELSE ( date + 001 00:00:00:000 )
The THEN part returns a date value (data type: date)
The ELSE part returns a date/time value (data type: date/time)
Modify the above expression into:
IF ( column <> 1 ) THEN ( date + 000 00:00:00:000 ) ELSE ( date + 001 00:00:00:000 )
The THEN part returns a date/time value.
The ELSE part returns a date/time value.
or
IF ( column <> 1 ) THEN ( date ) ELSE ( datetime-to-date ( date + 001 00:00:00:000 ) )
The THEN part returns a date value.
The ELSE part returns a date value.
参见:
http://www-01.ibm.com/support/docview.wss?uid=swg21425745
举例分析:
select unique case when month(statdate) <10 then 0||month(statdate) else ''||month(statdate) end statdate from disxxx
where statdate = '20130731' or statdate = '20121231';
select unique case when month(statdate) <10 then 0||month(statdate) else month(statdate) end statdate from disxxx
where statdate = '20130731' or statdate = '20121231'
0||month(statdate) 这个得到的 是一个字符串
month(statdate) 这个得到的是个 数字。
两个冲突!