How to use TEXT column inside CASE statement
Hi,
I am using Informix 11.70 server and getting error when I run this query :
SELECT CASE WHEN A.MY_TEXT IS NULL THEN
B.MY_TEXT ELSE A.MY_TEXT END
FROM Table1 A,Table2 B
where A.ID=B.ID
error:
615: Blobs are not allowed in this expression
This error suggests that we can't use TEXT inside CASE statement.
Need help on rewriting the same query which returns result as expected above.
Thanks in advance.
Pradeep
Hello.
According to the docs....
LimitationsYou cannot use TEXT operands
in arithmetic or string expressions, nor can you assign literals to
TEXT columns in the SET clause of the UPDATE statement.
You
also cannot use TEXT values in any of the following ways: With aggregate
functionsWith the IN clauseWith the MATCHES or LIKE clausesWith the GROUP BY
clauseWith the ORDER BY clause
You cannot use a quoted text string, number, or any other
actual value to insert or update TEXT columns.
Important: An error results if you try to
return a TEXT column from a subquery, even if no TEXT column is used
in a comparison condition or with the IN predicate.
Maybe you could do two separated selects, with UNION ALL, ignoring the NULL
values ???
Hope it helps.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70
IBM Information Management Informix Technical Professional
IBM Infosphere DataStage Technical Professional
Database Administrator - Cleartech Ltda
BRIUG website administrator
Informix independent consultant
(cel) +55 11 7603-0358
> To: ids@iiug.org
> From: pkyadav1@hotmail.com
> Subject: How to use TEXT column inside CASE statement [27645]
> Date: Wed, 18 Jul 2012 08:28:59 -0400
>
> Hi,
> I am using Informix 11.70 server and getting error when I run this query :
>
> SELECT CASE WHEN A.MY_TEXT IS NULL THEN
> B.MY_TEXT ELSE A.MY_TEXT END
> FROM Table1 A,Table2 B
> where A.ID=B.ID
>
> error:
> 615: Blobs are not allowed in this expression>
> This error suggests that we can't use TEXT inside CASE statement.
>
> Need help on rewriting the same query which returns result as expected above.
>
> Thanks in advance.
> Pradeep
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>