CODEWITHSUNDEEP
sqlblobinformix
Anyname Donotcare
Anyname Donotcare

Reputation: 11403

Blobs are not allowed in this expression

I face the following problem:

-615: Blobs are not allowed in this expression.

SELECT ser
      ,task_code
      ,trans_serial
      ,trans_year
      ,CASE when nvl(length(signed_content), 0) <> '' THEN  signed_content  ELSE  attach_content  END AS signed_content
      ,CASE WHEN nvl(length(signed_content), 0) <> '' THEN 'signed_content' ELSE 'attach_content' END AS comes_from
FROM attach_detail
WHERE serial = 5 AND task_code = 88 AND trans_year = 2012 AND trans_serial = 23728

Note: signed_content, attach_content are of type BYTE; the other columns are of type INT.

Upvotes: 1

Views: 2259

Answers (1)

CL.
CL.

Reputation: 180162

Your problem appears to be that Informix does not allow blobs inside CASE expressions.

To work around this, you could transform the CASE into two subqueries:

SELECT ser
      ,task_code
      ,trans_serial
      ,trans_year
      ,signed_content AS signed_content
      ,'signed_content' AS comes_from
FROM attach_detail
WHERE nvl(length(signed_content), 0) <> ''
  AND serial = 5 AND task_code = 88 AND trans_year = 2012 AND trans_serial = 23728
UNION ALL
SELECT ser
      ,task_code
      ,trans_serial
      ,trans_year
      ,attach_content AS signed_content
      ,'attach_content' AS comes_from
FROM attach_detail
WHERE nvl(length(signed_content), 0) = ''
  AND serial = 5 AND task_code = 88 AND trans_year = 2012 AND trans_serial = 23728

Upvotes: 2

Related Questions