SQL to delete all rows for a group except the most current

cliff boley

SQL to delete all rows for a group except the most current
Hi all,
my SQL skills are mediocre at best.
I've been fooling around with an SQL that will delete all rows for a group
of columns except the most
current (timestamp) row.

Does anyone have a eloquent technique that will do this with a dynamic SQL
or a REXX?


I did get close with a REXX but the SQL won't recognize (-180 error) the
REXX host variable with a timestamp value from
a previous select.
ie. in the REXX SQL "....AND STAT_TIME < 'MAXTIME' "

How do you tell SQL that the REXX var MAXTIME is in timestamp format??

thanks for any help,
cliff:-)



Michael Ebert

Re: SQL to delete all rows for a group except the most current
(in response to cliff boley)
Hi Cliff,

it's a REXX problem. The way you've coded it, you're comparing the
Timestamp column with a Char string 'MAXTIME'. You want to resolve the
REXX var MAXTIME here. Add two double quotes:
"....AND STAT_TIME<'"MAXTIME"'"

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Hi all,
my SQL skills are mediocre at best.
I've been fooling around with an SQL that will delete all rows for a group
of columns except the most
current (timestamp) row.

Does anyone have a eloquent technique that will do this with a dynamic SQL
or a REXX?


I did get close with a REXX but the SQL won't recognize (-180 error) the
REXX host variable with a timestamp value from
a previous select.
ie. in the REXX SQL "....AND STAT_TIME < 'MAXTIME' "

How do you tell SQL that the REXX var MAXTIME is in timestamp format??

thanks for any help,
cliff:-)

Joe DeCastro

Re: SQL to delete all rows for a group except the most current
(in response to Michael Ebert)
<snip>
Hi all,
my SQL skills are mediocre at best.
I've been fooling around with an SQL that will delete all rows for a group
of columns except the most
current (timestamp) row.
<snip>

Cliff,

I am including an SQL select as an example of how to include ( = ) or exclude ( < ) the most current timestamp. To specify the timestamp data type in REXX you may need to use an SQLDA.

HTH,
Joe

SELECT DBNAME, TSNAME, TIMESTAMP, HEX(START_RBA)
FROM SYSIBM.SYSCOPY
WHERE DBNAME = 'DISASTER'
AND TSNAME = 'RCVRYTS1'
AND ICTYPE = 'Q'
AND TIMESTAMP = (SELECT MAX(TIMESTAMP)
FROM SYSIBM.SYSCOPY
WHERE DBNAME = 'DISASTER'
AND TSNAME = 'RCVRYTS1'
AND ICTYPE = 'Q')



cliff boley

Re: SQL to delete all rows for a group except the most current
(in response to Joe DeCastro)
Thanks to Dr. Michael and Joe.
Your suggestions did the trick.
cliff:-)

-----Original Message-----
From: Dr. Michael Ebert [mailto:[login to unmask email]
Sent: Friday, December 27, 2002 11:40 AM
To: [login to unmask email]
Subject: Re: SQL to delete all rows for a group except the most current



Hi Cliff,

it's a REXX problem. The way you've coded it, you're comparing the Timestamp
column with a Char string 'MAXTIME'. You want to resolve the REXX var
MAXTIME here. Add two double quotes:
"....AND STAT_TIME<'"MAXTIME"'"

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Hi all,
my SQL skills are mediocre at best.
I've been fooling around with an SQL that will delete all rows for a group
of columns except the most
current (timestamp) row.

Does anyone have a eloquent technique that will do this with a dynamic SQL
or a REXX?


I did get close with a REXX but the SQL won't recognize (-180 error) the
REXX host variable with a timestamp value from
a previous select.
ie. in the REXX SQL "....AND STAT_TIME < 'MAXTIME' "

How do you tell SQL that the REXX var MAXTIME is in timestamp format??

thanks for any help,
cliff:-)