I only use ODBC escape sequences when necessary. Obviously I want to use standard sql syntax as much as possible. {fn user() } is handy because it works in all the databases that I work with and there is no substitute standard sql function for getting current userid that is cross-dbms.

I also use {fn now()} which works across most ODBC drivers, but I can't in this case because I need to use a constant timestamp value so as not to change distinctness of the subquery that is the source for the insert.

The datetime ISO-standard string format I am using works in all the databases I use 14+ (including PG), except in this case where 'distinct' is used with subquery in PG.

Also not all PG clients use ODBC, so other PG clients will encounter this issue using standard ISO datetime string format when not using ODBC. I don't want to limit users to using ODBC for loading/updating the database by running sql scripts (which is what the sql for this issue is used for) so almost all of the database update/load scripts use generic sql where timestamp/datetime values are are written as ISO datetime format strings same format as '2010-04-30 00:00:00'

BTW, I have also tested the sql in question with SQLite, MS Access, MS Excel and Sybase Adaptive Server 15 and it works with no error, so now that's 14 different DBMSs that have no issue with the ISO standard string format and distinct keyword.

I guess I can find some workaround for this to work with ODBC just for the specific sql statements causing errors with PG, but that does not resolve the issue for PG clients not using ODBC.

Like I said, I am reporting this issue so it can be identified and hopefully addressed at some point in the future, it is not critical for me for it to work right now, but that would be nice otherwise user will see a bunch of one-time errors and lose some ease of use but otherwise will not be too badly affected.

Farid

On 6/4/2010 9:42 PM, Kris Jurka wrote:


On Fri, 4 Jun 2010, Farid Zidan wrote:

Here is actual statements I am running and like I said they work for all 9+
DBMSs (I use ODBC and{fn user()} is the ODBC cross-dbms syntax for the
current user ID):

'2010-04-30 00:00:00',
'2010-04-30 00:00:00',
{fn user() }


If you're into using standard ODBC escapes for portability, shouldn't you be using {ts '2010-04-30 00:00:00'}?

http://msdn.microsoft.com/en-us/library/ms712360%28VS.85%29.aspx

Kris Jurka



--
Signature www.zidsoft.com CompareData:  compare and synchronize SQL DBMS data visually between two databases using ODBC drivers