This must have something to do with the multiple table involvement.
I modified my 'insert' command to include the updated field on the
INSERT INTO tmp_rxpurge (rxnum,dt_wrt,dt_lastfill,b_purge)
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
1. UPDATE tmp_rxpurge SET dt_lastact=dt_wrt
2. UPDATE tmp_rxpurge SET dt_lastact=dt_lastfill where dt_lastfill IS
NOT NULL and dt_lastfill > dt_lastact
3. UPDATE tmp_rxpurge SET b_purge='t' where dt_lastact < '2010-01-01'
I'm still puzzled why the first method didn't work.
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?
In response to
pgsql-odbc by date
|Next:||From: Hiroshi Inoue||Date: 2012-04-12 13:24:09|
|Subject: Re: Passing numeric Bind variables to ODBC driver convers
to "Double precision"|
|Previous:||From: Ken Benson||Date: 2012-04-11 22:01:36|
|Subject: UPDATE table via ODBC fails.|