Bugzilla uses these all over the place... most of them are things like "(column IS NOT NULL)" which you can work around by just retreiving "column" and using defined() on the result instead of just directly looking at it. Some are not quite so easy, like the following SQL that gets run by processmail when I submit a bug: SELECT userid, (refreshed_when > 'Jan 1 1900 12:00AM') FROM profiles WHERE login_name = 'justdave@netscape.com'

Dave, do these work if you add an AS to the statement? SELECT userid, (refreshed_when > 'Jan 1 1900 12:00AM') AS recent FROM profiles WHERE login_name = 'justdave@netscape.com'

Yes, we tried that. It'll do numeric math (SELECT column1, column2 + 5, ...) but it won't do boolean stuff. With or without an AS.

Is there a technote about this somewhere?

Can these be changed to IF(conditon,1,0) ???

Re: comment 3 http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookView See Chapter 2, under the heading "Choosing columns: The select clause" Note that mathematical and bitwise operators are allowed, nowhere does it mention boolean operators. And my brute force trying it on a live server agrees that they don't work.

Re: comment 4 Nice thought, but Sybase apparently has no IF() function or equivalent, either.

Also WRT comment #4: PostgreSQL also doesnt support that. It would be: CASE WHEN condition THEN 1 ELSE 0

FYI, Sybase 11.5 added support for CASE. CASE is the way to go since it's pretty standard. Alternatively, you can emulate the boolean logic using a complex ISNULL() function.

Yeah, the syntax dkl posted in comment 7 for Postgres also works in Sybase. We ended up using that in several places on Zippy's Bugzilla.

There should be a function for it in DBCompat.pm actually... SQLIf() or something like that.

Jeff says boolean stuff doesn't work in the SELECT part on Oracle, either. CASE WHEN works the same as both Postgres and Sybase however.

"CASE WHEN" works in Oracle 9i and Oracle 10g as well. boolean expressions in the select list must be replaced with something that produces a number (0/1) or a String (VARCHAR2 in Oracle) - e.g. ('Y','N'; or 'T'/'F'). bug 248001 already deals with some boolean expressions in the where clause.

What's up with this? I've noticed that we've switched to CASE WHEN for almost all our new code. Are there still places in the code where this is a problem?

(In reply to comment #13) > What's up with this? I've noticed that we've switched to CASE WHEN for almost > all our new code. Are there still places in the code where this is a problem? Yes, and as stated earlier, bug 248001 fixed a lot of them. But I recall there are still few places where this is a problem. I plan to dig the patch up from my install and post it here, when I have time.

(In reply to comment #14) > (In reply to comment #13) > > What's up with this? I've noticed that we've switched to CASE WHEN for almost > > all our new code. Are there still places in the code where this is a problem? > > Yes, and as stated earlier, bug 248001 fixed a lot of them. But I recall there > are still few places where this is a problem. I plan to dig the patch up from my > install and post it here, when I have time. You were right, there doesn't seem to be any left. And if there are, we can always reopen, or create a new bug.