|
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? |
|
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:) 1
Thanks for the votes: You like to learn from my mistakes, don't you:) Feel free to tell about your
(19 Mar '13, 12:47)
Volker Barth
1
It should be noted that GREATER() and LESSER() are vendor extensions (as documented), so for portable SQL, one would probably need to
Aside: I had thought of suggesting an IF expression here - but that's another one of these very helpful vendor extension itself:)
(21 Mar '13, 06:44)
Volker Barth
|