Alan-
I don't think you can have more than one *column* in your subselect. I think it is your b.* that is causing the problems.
-Tom Stieger
Engineer
California Fine Wire
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, November 04, 2010 6:23 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Using SQL to delete "duplicate" record
Morning all
I have a file that is keyed, but I have multiple records with the same key that I need to delete but keep the latest record Fortunately there is a create "date" on the file (really a 6 digit numeric field in mmddyy format)
here is my sql statement
Delete From FILE1 A
Where RRN(A) in
(Select RRN(B),
b.* From FILE1b, FILE1 C
Where B.ord# = C.ord# and
substr(digits(b.crtdat), 5, 2) || substr(digits(b.crtdat), 1, 4) < substr(digits(c.crtdat), 5, 2) || substr(digits(c.crtdat), 1, 4))
Unfortunately I am receiving the following message Subselect with more than one result column not valid.
I checked and confirmed that for the same Ord#, there are more than 2 records (5 or 6 in some cases)
Ord# is the only field that is the key. The other fields on the record have no bearing between the duplicate ord# (Hopefully that last sentence makes
sense)
Anyone have any ideas how I can circumvent this problem Alan Shore Programmer/Analyst, Direct Response E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
