l.nethaji
New User
Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu
|
|
|
|
This is the brief description of the problem scenario :
Lets consider the following is the table T1 with some 6 records.
c1 c2 c3 c4 C5 C6
31 A null 4 null 6/19/2007
11 A null 4 null 3/12/2008
22 A null 4 0 6/27/2007
41 A 0 4 0 3/17/2008
45 A 0 4 0 6/28/2008
54 A 0 4 null 6/28/2007
I have to group the similar records based on( c2,c3,c4,c5) c1 is the primary key.If all these columns from c2 to c5 are similar I must group them and get the max(c6) data in it.
once i get the record having the max timestamp ie max(c6) i must delete the remaining records which i got in the group by clause.
The columns from c2 to c5 is nullable columns
I am using select using the following query
Query Q1:
Select c2,c3,c4,c5 ,max(c6),count(*) as cn
From t1 group by c2,c3,c4,c5
Having count(*) >1
The result set I get is
c2 c3 c4 C5 C6 CN
A null 4 null 3/12/2008 2
A 0 4 0 6/28/2008 2
I am fetching this into temp working storage variable wc2,wc3,wc4,wc5,wc6,wcn
if the values are null i am moving this to null indicator (ind-c2,ind-c3,ind-c4,ind-c5)
Now if I want to write a query to delete the remaining records got in the group by clause.
ie remaining records which are to be deleted are
c1 c2 c3 c4 C5 C6
31 A null 4 null 6/19/2007
41 A 0 4 0 3/17/2008
Since these records only come under group by clause
Query Q2:
The following is the query I am using for deletion where I am giving the input from query Q1 to query Q2.
delete from t 1
where
C2= :wc2 :ind-c2 and
C3=:wc3 :ind-c3 and
C4=:wc4 :ind-c4 and
C5=:wc5 :ind-c5 and
C6 < : wc6 ( timestamp field)
If I am using this query I am not able to get the correct result as c3 and c5 can be null or any numeric value
The query must work for both null and numeric value .
I am getting -305 error as I am not able to fetch the null values.
I am not able to delete the required records . the records that should not be deleted are
The records that should not be deleted are these records doesn’t come under group by clause
c1 c2 c3 c4 C5 C6
22 A null 4 0 6/27/2007
54 A 0 4 null 6/28/2007
Please help me out to resolve this issue.
Thanks in advance
L.Nethaji |
|
