charan_leo
asked on
SYBASE PROBLEM HELP!!!!!!!!
Hi
I am firing the following sql:
select emp_id,name,isnull (comments3,"") from Table1
here the comments3 is of IMAGE datatype.
I am getting the following error:
Invalid operator for datatype op: BUILTIN FUNCTION type: IMAGE.
But the same things works on MS-SQL. If anyone has some idea please let me know.
please do let me know
thanks in advance
charandeep
I am firing the following sql:
select emp_id,name,isnull (comments3,"") from Table1
here the comments3 is of IMAGE datatype.
I am getting the following error:
Invalid operator for datatype op: BUILTIN FUNCTION type: IMAGE.
But the same things works on MS-SQL. If anyone has some idea please let me know.
please do let me know
thanks in advance
charandeep
Jan Franek
Try to replace 'isnull(comments3,"")' with 'case when comments3 is null then "" else comments3 end' - it should be equivalent.
From the Sybase ASE Reference Manual vol. 1, Ch. 2 "TransactSQL Functions":
Image datatypes can only be converted to unichar, univarchar, binary or varbinary, and the first two only if the server's default character set is set to UTF-8.
All datatypes returned in a CASE statement must be implicitly convertible, so combining any image value with "" (which is a varchar) won't work.
We also can't ever ask if an image datatype is null, but we can ask if it is zero-length. (Strictly speaking a NULL value requires one byte, but ASE 12.5.0.3 seems to disregard this in image columns.)
What does seem to work (I am testing in ASE 12.5.0.3) is :
select emp_id
, name
, case when datalength(comments3) = 0
then ""
else convert(varchar, convert(varbinary, comments3))
from Table1
This however will truncate the comments3 column.
The column name "comments3" suggests text data, yet an image datatype is basically for unformatted binary data. You would have more options if the columns datatype were text instead of image - your original code would work, as text is implicitly convertible with (var)char while image is not.
Summary: The image datatype (deliberately) does not support string functions, and is not convertible with most other datatypes.
Image datatypes can only be converted to unichar, univarchar, binary or varbinary, and the first two only if the server's default character set is set to UTF-8.
All datatypes returned in a CASE statement must be implicitly convertible, so combining any image value with "" (which is a varchar) won't work.
We also can't ever ask if an image datatype is null, but we can ask if it is zero-length. (Strictly speaking a NULL value requires one byte, but ASE 12.5.0.3 seems to disregard this in image columns.)
What does seem to work (I am testing in ASE 12.5.0.3) is :
select emp_id
, name
, case when datalength(comments3) = 0
then ""
else convert(varchar, convert(varbinary, comments3))
from Table1
This however will truncate the comments3 column.
The column name "comments3" suggests text data, yet an image datatype is basically for unformatted binary data. You would have more options if the columns datatype were text instead of image - your original code would work, as text is implicitly convertible with (var)char while image is not.
Summary: The image datatype (deliberately) does not support string functions, and is not convertible with most other datatypes.
Hi, charandeep, how did you go with this?
ASKER
I have tried what u guys said
but it hasn't worked out accordingly man
still lookin ????
but it hasn't worked out accordingly man
still lookin ????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.