>Thanks for all the suggestions, I'm actually gunning for portability but the problem is I am generating this query pro grammatically depending on some circumstances .. <<
That is almost always a bad idea. It is slow, it says that you have
no control over the execution of your application, etc. -- you are ad
libbing, which can be funny in a comedy show, but not so good in a
database
There is as defined by ANSI and ISO; it is supposed to run anywhere
and I find it is much easier to write than most "one product cowboy
coders" think. Then there is portable SQL; it has mild dialect
features that can be mapped to Standard SQL, so the conversion is
mechanical (getdate() =CURRENT_TIMEST AMP, etc). Then there is pure
dialect and you are screwed (Oracle's CONNECT BY).
That is almost always a bad idea. It is slow, it says that you have
no control over the execution of your application, etc. -- you are ad
libbing, which can be funny in a comedy show, but not so good in a
database
>and I need to do it in such a way that the code that creates it will be the same on oracle, mysql and mssql. <<
and I find it is much easier to write than most "one product cowboy
coders" think. Then there is portable SQL; it has mild dialect
features that can be mapped to Standard SQL, so the conversion is
mechanical (getdate() =CURRENT_TIMEST AMP, etc). Then there is pure
dialect and you are screwed (Oracle's CONNECT BY).