UPDATE:
This must have something to do with the multiple table involvement.
I modified my 'insert' command to include the updated field on the initial insert.
INSERT INTO tmp_rxpurge (rxnum,dt_wrt,dt_lastfill,b_purge)
SELECT "RXNUM","DATE",rf.dt_lastfill,'f'
FROM rx
LEFT JOIN (SELECT "RXNUM" AS rxnum, Max("DATE") as dt_lastfill FROM transactions GROUP BY "RXNUM")
as rf on rf.rxnum=rx."RXNUM"
Then - created three additional update steps ... they all worked via the ODBC connector...- UPDATE tmp_rxpurge SET dt_lastact=dt_wrt
- UPDATE tmp_rxpurge SET dt_lastact=dt_lastfill where dt_lastfill IS NOT NULL and dt_lastfill > dt_lastact
- UPDATE tmp_rxpurge SET b_purge='t' where dt_lastact < '2010-01-01'
I'm still puzzled why the first method didn't work.
Ken
On 4/11/2012 3:01 PM, Ken Benson wrote:
I have a fairly straightforward routine which uses an ODBC connection to do 4 steps:
- DROP TABLE IF EXISTS tmp_rxpurge
- CREATE TABLE tmp_rxpurge (rxnum VARCHAR(300),dt_wrt DATE,dt_lastfill DATE,dt_lastact DATE, b_purge BOOLEAN)
- INSERT INTO tmp_rxpurge (rxnum,dt_wrt,b_purge) SELECT "RXNUM","DATE",'f' FROM rx
- UPDATE tmp_rxpurge as t SET dt_lastfill = rf.dt_lastfill FROM (SELECT "RXNUM" AS rxnum, Max("DATE") as dt_lastfill FROM transactions GROUP BY "RXNUM") as rf WHERE rf.rxnum=t.rxnum
The first three commands are executed perfectly as expected.
The update fails to do anything.
There is no error generated.
I have enabled 'logging' in the ODBC connector - and I don't see anything out of the ordinary in the short-log. (The long-log - I have no idea what all that stuff means.)
If I copy that last command into a psql window - it executes - and works fine.
But - it will not work via the ODBC connector.
What's going on?
Ken