Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

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
0
charan_leo
Asked:
charan_leo
  • 3
1 Solution
 
Jan FranekCommented:
Try to replace 'isnull(comments3,"")' with 'case when comments3 is null then "" else comments3 end' - it should be equivalent.
0
 
Joe WoodhousePrincipal ConsultantCommented:
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.
0
 
Joe WoodhousePrincipal ConsultantCommented:
Hi, charandeep, how did you go with this?
0
 
charan_leoAuthor Commented:
I have tried what u guys said
but it hasn't worked out accordingly man
still lookin ????
0
 
Joe WoodhousePrincipal ConsultantCommented:
Using "datalength" in a CASE didn't help? Hmmm.

Do you have any control over the datatypes? Text would really suit this better than image. Sybase treats the image datatypes as a big BLOB of raw binary, which is why most of your functions are failing on it.

And oops. I see there was a simple type in my suggestion above, I somehow left out the "end" needed to close the CASE!! Try this:

select  emp_id
,         name
,         case   when   datalength(comments3) = 0
                    then    ""
                    else     convert(varchar(255), convert(varbinary(255), comments3))    -- increased length to 255
          end     -- this is the bit that was missing
from   Table1

It won't work for any rows where comments3 is larger than 255 bytes.

Let us know if that works. Good luck!
 
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now