First of all, I'm running 10.1B, with the Progress OpenEdge 10.1B driver version 5.20.00.48.
Ultimately, the query I'm trying to run would look something like this:
UPDATE pub.mytable SET myDateTime1 = to_timestamp('01/01/07 10:00:00.000') WHERE myID IN (SELECT MAX(myDateTime2) FROM pub.mytable WHERE someField = 'someValue')
I can write this in several different ways, but the fundamental error I just can't get around is that simply saying:
UPDATE pub.mytable SET myDateTime1 = to_timestamp('01/01/07 10:00:00.000')
does not work. Before you blame it on "to_timestamp(...)", I can confirm that
SELECT TOP 1 to_timestamp('01/01/07 10:00:00.000') FROM pub.scan
works as intended. SELECT to_timestamp(...) alone does not. There seems to be some weird limitations on support for function calls. Anyway, since it works in a select statement, I figured i'd make do and try:
UPDATE pub.mytable SET myDateTime1 = (SELECT TOP 1 to_timestamp('01/01/07 10:00:00.000') from pub.scan). It didn't like the TOP clause suddenly for some odd reason, so I used a where clause to narrow the result down to one row for a final product of:
UPDATE pub.scan SET myDateTime1 = (SELECT to_timestamp('01/01/07 10:00:00.000') from pub.scan WHERE myID = 1)
However, even in this format, it won't accept the to_timestamp function, even though the EXACT same SELECT statement run alone without the update works as expected.
Has Progress gone and written out entirely separate and isolated routines for parsing update and select statements (with update statements clearly receiving a good deal less functionality)?
I've ran into some really stupid bugs and quirks in progress' sql engine, but this one is pretty much a showstopper for me.
Oh, and this problem extends beyond just to_timestamp. I also tried convert('DATETIME', '.....'), and some other non date related functions, all exhibit identical behavior (work fine in select statements, but not in update, even when embedded in a select).
Any fixes, workarounds, ideas?
Ultimately, the query I'm trying to run would look something like this:
UPDATE pub.mytable SET myDateTime1 = to_timestamp('01/01/07 10:00:00.000') WHERE myID IN (SELECT MAX(myDateTime2) FROM pub.mytable WHERE someField = 'someValue')
I can write this in several different ways, but the fundamental error I just can't get around is that simply saying:
UPDATE pub.mytable SET myDateTime1 = to_timestamp('01/01/07 10:00:00.000')
does not work. Before you blame it on "to_timestamp(...)", I can confirm that
SELECT TOP 1 to_timestamp('01/01/07 10:00:00.000') FROM pub.scan
works as intended. SELECT to_timestamp(...) alone does not. There seems to be some weird limitations on support for function calls. Anyway, since it works in a select statement, I figured i'd make do and try:
UPDATE pub.mytable SET myDateTime1 = (SELECT TOP 1 to_timestamp('01/01/07 10:00:00.000') from pub.scan). It didn't like the TOP clause suddenly for some odd reason, so I used a where clause to narrow the result down to one row for a final product of:
UPDATE pub.scan SET myDateTime1 = (SELECT to_timestamp('01/01/07 10:00:00.000') from pub.scan WHERE myID = 1)
However, even in this format, it won't accept the to_timestamp function, even though the EXACT same SELECT statement run alone without the update works as expected.
Has Progress gone and written out entirely separate and isolated routines for parsing update and select statements (with update statements clearly receiving a good deal less functionality)?
I've ran into some really stupid bugs and quirks in progress' sql engine, but this one is pretty much a showstopper for me.
Oh, and this problem extends beyond just to_timestamp. I also tried convert('DATETIME', '.....'), and some other non date related functions, all exhibit identical behavior (work fine in select statements, but not in update, even when embedded in a select).
Any fixes, workarounds, ideas?
