Solved

SYBASE PROBLEM HELP!!!!!!!!

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A procedure for exporting installed hotfix details of remote computers using powershell
What is Backup? Backup software creates one or more copies of the data on your digital devices in case your original data is lost or damaged. Different backup solutions protect different kinds of data and different combinations of devices. For e…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now