Solved

SYBASE PROBLEM HELP!!!!!!!!

Posted on 2004-10-08
7
267 Views
Last Modified: 2006-11-17
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
Comment
Question by:charan_leo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
7 Comments
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 12258390
Try to replace 'isnull(comments3,"")' with 'case when comments3 is null then "" else comments3 end' - it should be equivalent.
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 12294445
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 12520742
Hi, charandeep, how did you go with this?
0
 

Author Comment

by:charan_leo
ID: 12520781
I have tried what u guys said
but it hasn't worked out accordingly man
still lookin ????
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 50 total points
ID: 12520848
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
This article summaries thoughts and ideas from two years of sustained use. It provides good reasoning to make the jump to Windows 10.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question