Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SYBASE PROBLEM HELP!!!!!!!!

Posted on 2004-10-08
7
Medium Priority
?
295 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 200 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Whoever said that “a picture is worth one thousand words” observed a fact that can dramatically affect your marketing success. Most people tend to learn visually, so many publishers commonly acknowledge the effectiveness of visual learning by using…
Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

721 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