How to use MAX() as a scalar function?

[VolkerBarth]

Preface: That's one of the SQL pitfalls I've stumbeld over too often...

Well, sometimes I've to turn expressions from an ordinary programming language into a SQL query, and sometimes these expressions contain the classical scalar max() function, say like "where max(expr1, expr2) > 0".

The following (absolutely senseless) query would be a sample:

select * from systab
where max(table_page_count, ext_page_count) > 10;

Of course, using max() that way in a SQL query is doomed to fail, as MAX() is an aggregate function and works on rows of data, not on two (or more) separate expressions, so this raises

Question: Is there a way to use max() as intended here?

[VolkerBarth · Accepted solution]

Well, you cannot use MAX() for that - but of course SQL Anywhere has according support:

Use GREATER() as a scalar MAX() and LESSER() as a scalar MIN(), such as

select * from systab
where greater(table_page_count, ext_page_count) > 10

---

And now I hope I won't forget that anymore:)

[VolkerBarth · Accepted solution]

Thanks for the votes: You like to learn from my mistakes, don't you:)

Feel free to tell about your favourite pitfallslearning experiences, too...

[VolkerBarth · Accepted solution]

It should be noted that GREATER() and LESSER() are vendor extensions (as documented), so for portable SQL, one would probably need to

• use a CASE expression ("case when isnull(table_page_count, 0) > isnull(ext_page_count, 0) then ...") or
• try to re-state the logic by ORing expressions or the like.

Aside: I had thought of suggesting an IF expression here - but that's another one of these very helpful vendor extension itself:)