Duplicate Row Issue
Moderators: chulett, rschirm, roy
Why isnt reverse sorting possible? Any who,
Another approach is , that you can write to a hashed file and reference the same hashed file. If the key is found dont write it again. Hence your constraint will be reflink.NOTFOUND.
Make sure your hashed file is locked for updates.
Another approach is , that you can write to a hashed file and reference the same hashed file. If the key is found dont write it again. Hence your constraint will be reflink.NOTFOUND.
Make sure your hashed file is locked for updates.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You don't - can't - have multiples of the key value in a hashed file. They way they work relies absolutely upon having unique keys.
Therefore, you will need a different approach - perhaps a secondary key lookup via a UV stage from a hashed file with an artificial key. If you do that, you can readily constrain for the first matching record (you will need to sort the result set, of course, using an ORDER BY clause - you can use FIRST 1 as the additional constraint).
Therefore, you will need a different approach - perhaps a secondary key lookup via a UV stage from a hashed file with an artificial key. If you do that, you can readily constrain for the first matching record (you will need to sort the result set, of course, using an ORDER BY clause - you can use FIRST 1 as the additional constraint).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Not sure what having two columns in the hashed file has to do with the ability to sort. You stated that when you build a hashed file and send more than one row for a particular key, the last one in is what is retained. That is correct due to the 'destructive overwrite' nature of hashed files.rajkraj wrote:Thanks for your quick reply, but sorting is not possible because I have only 2 columns in hash file (member_key,sese_id) and member_key is key column.
You then stated that you wanted the first record for any particular key to be the one 'saved'. You do that a couple of different ways. One way is to ensure that the first record per key arrives last, hence the 'reverse your sort order' bit of advice. There's no reason that can't happen.
Or you can arrange for the first record per key to be the only record per key written. This requires the use of stage variables and the ability to detect changing 'groups' - in your case, only allow the first occurance of member_key to be written to the hashed file.
Either approach will solve your problem as I've understood it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks for your reply...but how would we do 'reverse sort' on a single key.
Ex:Member_key Id_key
1234 23
1234 24
1234 25
When we store this data into a Hashed file it will keep the last record but I want the first record.
I used order by while extracting the data from table but it's not changing the order
Please advise me
Ex:Member_key Id_key
1234 23
1234 24
1234 25
When we store this data into a Hashed file it will keep the last record but I want the first record.
I used order by while extracting the data from table but it's not changing the order
Please advise me
chulett wrote:Not sure what having two columns in the hashed file has to do with the ability to sort. You stated that when you build a hashed file and send more than one row for a particular key, the last one in is what is retained. That is correct due to the 'destructive overwrite' nature of hashed files.rajkraj wrote:Thanks for your quick reply, but sorting is not possible because I have only 2 columns in hash file (member_key,sese_id) and member_key is key column.
You then stated that you wanted the first record for any particular key to be the one 'saved'. You do that a couple of different ways. One way is to ensure that the first record per key arrives last, hence the 'reverse your sort order' bit of advice. There's no reason that can't happen.
Or you can arrange for the first record per key to be the only record per key written. This requires the use of stage variables and the ability to detect changing 'groups' - in your case, only allow the first occurance of member_key to be written to the hashed file.
Either approach will solve your problem as I've understood it.
So, from your eample:
You would like the Id_key of 23 to be stored for Member_key 1234? You've said you've done an order by, now you just need to make the secondary sort descending. Something like:
That should make them arrive like this:
And solve your problem. Yes?
Code: Select all
Member_key Id_key
1234 23
1234 24
1234 25
Code: Select all
ORDER BY Member_key, Id_key desc
Code: Select all
Member_key Id_key
1234 25
1234 24
1234 23
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks for quick reply ....but my issue is still ther ...
Actually my data looks like this....
Member_key Id_key
1234 75
1234 abc
1234 %Agh
1234 =AVX
here order by is not useful...right....
Actually my data looks like this....
Member_key Id_key
1234 75
1234 abc
1234 %Agh
1234 =AVX
here order by is not useful...right....
chulett wrote:So, from your eample:
You would like the Id_key of 23 to be stored for Member_key 1234? You've said you've done an order by, now you just need to make the secondary sort descending. Something like:Code: Select all
Member_key Id_key 1234 23 1234 24 1234 25
That should make them arrive like this:Code: Select all
ORDER BY Member_key, Id_key desc
And solve your problem. Yes?Code: Select all
Member_key Id_key 1234 25 1234 24 1234 23
Would have been nice to have sample data that matched reality a little better.
You can still sort, yes. Just to beat this horsey a little longer...
Assign a record number to each row in a transformer, @INROWNUM will work nicely. Then use a Sort stage to sort ascending on your key field and descending on the record number. For example:
What will come out of the Sort stage will be:
It *can* be done.
You can still sort, yes. Just to beat this horsey a little longer...
Assign a record number to each row in a transformer, @INROWNUM will work nicely. Then use a Sort stage to sort ascending on your key field and descending on the record number. For example:
Code: Select all
Member_key Id_key Record
1234 75 1
1234 abc 2
1234 %Agh 3
1234 =AVX 4
Code: Select all
Member_key Id_key Record
1234 =AVX 4
1234 %Agh 3
1234 abc 2
1234 75 1
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
"THANKS A LOT" It would work.
quote="chulett"]Would have been nice to have sample data that matched reality a little better.
You can still sort, yes. Just to beat this horsey a little longer...
Assign a record number to each row in a transformer, @INROWNUM will work nicely. Then use a Sort stage to sort ascending on your key field and descending on the record number. For example:
What will come out of the Sort stage will be:
It *can* be done.[/quote]
quote="chulett"]Would have been nice to have sample data that matched reality a little better.
You can still sort, yes. Just to beat this horsey a little longer...
Assign a record number to each row in a transformer, @INROWNUM will work nicely. Then use a Sort stage to sort ascending on your key field and descending on the record number. For example:
Code: Select all
Member_key Id_key Record
1234 75 1
1234 abc 2
1234 %Agh 3
1234 =AVX 4
Code: Select all
Member_key Id_key Record
1234 =AVX 4
1234 %Agh 3
1234 abc 2
1234 75 1