DJ Dunk

iTrader: (5)

Hello,

I'm struggling to get my head around a little SQL query that I need to write in Oracle 8i.

Basically I have a table called "assets" this table has a "barcode" field and another field called "duplicate_barcode" which is populated with a "Y" where the barcode appears more than once in the table. On each record, as well as the barcode there are a number of descriptors, lets call them "desc1", "desc2" and "desc3".

What I need to do is pull out all records where "duplicate_barcode" = 'Y' (should pull out at least 2 records) and also "desc1" is the same as "desc1" on the other record. Desc2 and desc3 also need to be the same as on the other record. Hope that makes sense ?

My results should show records that duplicate and have the same 3 descriptors as well.

Can anyone help ?

Thanks,
Dunk

Dracoro

Join the table onto itself using the the descriptors as join fields.

e.g.

SELECT a.barcode

FROM assets a

INNER JOIN assets b
ON a.desc1 = b.desc1
AND a.desc2 = b.desc2
AND a.desc3 = b.desc3


As I read your post, the field that says 'duplicate barcode' is irrelevant in the query as the query is effectively returning the duplicates anyway. To test this, bring back the 'duplicate_barcode' field and do an outer join instead of the inner join. All those with null records against a 'duplicate_barcode' = y are 'lying' and all those with 'duplicate_barcode' that DO return records are also 'lying' Hope that makes sense.

DJ Dunk

iTrader: (5)

Thanks for that The duplicate barcode field is a little more complicated that it would first appear, it also checks other fields on the table as there can be 'genuine' duplicates. There is another field which indicates whether the record is a whole or part of a physical item and also other fields that indicate whether the duplicate is 'genuine'. So its pretty important that the duplicate_barcode field is used.

Would this make a difference to your suggested script ?

Many thanks for your help, I'm still pretty new to all this !

Dracoro

In the WHERE clause, put the conditions to include/exclude the appropriate records against table a.

In the join clause, put any appropriate conditions in if need be. e.g. if both records need to have duplicate_barcode = 'y' then the below is what you want.

SELECT a.barcode

FROM assets a

INNER JOIN assets b
ON a.desc1 = b.desc1
AND a.desc2 = b.desc2
AND a.desc3 = b.desc3
and b.duplicate_barcode = 'y'

WHERE a.duplicate_barcode = 'y'


In other words, start simple and test it. Build your way up adding conditions. For example, use the above and test for duplicates. Once you're happy this is so, then add statements to exlude 'genuine' duplicates (if that is what the requirement is??) either to the join condition or the where clause as appropriate. Venn diagrams can be useful before coding so you can be sure what you are bringing back from the code really is what you actually want.

DJ Dunk

iTrader: (5)

Excellent, many thanks I'll have a go and see what I can do. Appreciate your help.

DJ Dunk

iTrader: (5)

Sorry, but I'm still a bit stuck on this. I'll try to explain again . . . more for my own benefit !

Basically, I need to pull out the records where duplicate_barcode = 'Y' and only keep the ones where the 3 descriptors are all the same on the resulting record(s) that the barcode duplicates with.

For example, I want these:
But not these:
Still not sure if I'm making sense, so feel free to hurl abuse where deemed necessary

Dracoro

My last query above should do it, include the barcode in the join (AND a.barcode = b.barcode) as well.

DJ Dunk

iTrader: (5)

Thanks

DJ Dunk

iTrader: (5)

Hmmm, its still not returning the rows I'm expecting to see.

Its giving me the rows where the duplicate barcode field = 'Y' but its not checking the description fields to see if they match. Have I missed something ?

Dracoro

Can you put in your exact sql code. it 'could' be just a string comparison problem.

DJ Dunk

iTrader: (5)

Its the same as yours, but just with my actual field and tabe names.

DJ Dunk

iTrader: (5)

Anyone ?

Dracoro

Maybe the desc1/2/3 fields have different cases. Or there's spaces in the fields etc. Try applying trim/replace/upper functions etc. Check exactly what's being returned. i.e. it 'looks' like 'qew' but it might actually be ' qew ' if you know what I mean.

jpor

iTrader: (1)

I second that. Have you tried replacing '=' with the word "matches". to see if that brings anything back.

GCollier

Isn't the query posted above always going to match rows with themselves and return back every row that simply has duplicate_barcode = 'Y'?

Anyway, I think in Oracle 8i the following query will do what you want (I haven't run it though).

select assets.*
from assets,
(select barcode, desc1, desc2, desc3, count(*)
from assets
where duplicate_barcode = 'Y'
group by barcode, desc1, desc2, desc3
having count(*) > 1) duplicates
where assets.barcode = duplicates.barcode
and assets.desc1 = duplicates.desc1
and assets.desc2 = duplicates.desc2
and assets.desc3 = duplicates.desc3

If all you want is to see what is duplicated then pull out the 'duplicates' inline view, ie.

select barcode, desc1, desc2, desc3, count(*)
from assets
where duplicate_barcode = 'Y'
group by barcode, desc1, desc2, desc3
having count(*) > 1

Gary.

DJ Dunk

iTrader: (5)

Perfect !

Thanks Gary, you're correct, the previous script was doing as you said. Your new one took a little while to run as there are well over a million records in the table, but gave the exact result required.

Really appreciate yours and everyones help

Hol

iTrader: (1)

Is 8i still supported by Oracle?