Solved

SYBASE PROBLEM HELP!!!!!!!!

Posted on 2004-10-08
7
271 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

We asked our MSP customer base what their favorite tools were and how they help them serve clients. We focused our questions on favorite tools in the following categories: >PSA tools >RMM tools >Alert management tools >Communication tools and Mo…
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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