Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: checking if a image field is null

    Hi -

    I need to retrieve a value from a table only if another value in the row (a BLOB) is populated. I have a query that works in Oracle, SQLServer, and DB2, but does not work in Sybase, as follows:

    select INTFIELD
    from TABLE
    where IMAGEFIELD is not null
    and FIXEDCHARFIELD = 'some value'

    When I run this in Sybase, I get this error:

    TEXT and IMAGE datatypes may not be used in a WHERE clause, except with the LIKE expression.

    How can I get around this problem? The image field contains binary data, so I'm not sure what values I can use with a LIKE to make sure there is a value there. Also, is there anything that is database non-specific?

    Thanks for your help,
    -Korin

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Interesting. What ASE version are you on? You'd think that if the image column is nullable that you'd be able to do WHERE imgcol IS NULL. That might be something to kick up to Sybase Support as a new feature request (or ISUG if you're a member).

    You might see if the text ptr is invalid:
    http://manuals.sybase.com/onlinebook...709;pt=28709#X
    Thanks,

    Matt

  3. #3
    Join Date
    Aug 2002
    Location
    Omaha, Nebraska
    Posts
    21
    Originally posted by MattR
    Interesting. What ASE version are you on? You'd think that if the image column is nullable that you'd be able to do WHERE imgcol IS NULL. That might be something to kick up to Sybase Support as a new feature request (or ISUG if you're a member).

    You might see if the text ptr is invalid:
    http://manuals.sybase.com/onlinebook...709;pt=28709#X
    To determine if an IMAGE/TEXT field is "null" or empty, use the datalength function (which is the documented way to do this), ie:

    /* check for null, empty image_col */
    select x,y,z
    from table
    where datalength(image_col) = 0

    /* check for non-null */
    select x,y,z
    from table
    where datalength(image_col) > 0

  4. #4
    Join Date
    Apr 2004
    Posts
    2

    thanks

    Thanks for the advice - I have posted this request to ISUG (now that I know what it is).

    We are using ASE 12.5. I could not find any database independent way to get this information, so I am pulling all the image values out of the database and running through them in code to weed out the null values. :(

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •