[SQL ERROR 800]Corresponding types must be compatible in CASE expression.

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) 这个得到的是个 数字。

两个冲突!

上一篇:数据结构与算法(C/C++版)【数组】


下一篇:Google开源库-Volley的使用