Access/Sybase oddity

[Guest]

We use Sybase version 12.5. In the passthrough queries I write, if I use the
SQL expression "if ... in (null, '')" Access gets it wrong.

One common use of this is to generate names from first, middle initial and
last names in the database.

We use the SQL statement "first + ' ' + case when initial in (null, '') then
'' else '. ' end + last", which all our other utilities interpret correctly.

With Access the name always appears as "first . last". If I change the "if
.... in (null, '')" to, for example, "if initial = '' or initial is null",
Access returns the correct answer.

I've tried recreating the passthrough query and a few other things of that
kind, but nothing makes any difference.

Is this a bug or am I just missing something? If it's a bug, does anyone
know how I might report it?

TIA!

 

[Douglas J. Steele]

By many people's definition, it's Sybase that's misbehaving here: it's not
considered appropriate to say Null = Null is True, which is what in (null,
'') means. <g>

However, I am surprised that it's not working with pass-through queries: the
server's conventions are supposed to hold there. Are you sure you have the
most recent Sybase drivers installed?

 

[Van T. Dinh]

If you use PassThrough Queries, Access simply passes the entire SQL Strings
for Sybase to process the SQL and accept the return from Sybase in full.

Since it is Sybase that processes the SQL String, you need to write the SQL
Strings in Sybase SQL syntax, not Access.