RE: Better way to write this query

First you say you want to find a given UPC with differing items, but, then you write the query to look for exact duplicates. Are you looking for exact duplicates in addition to duplicate UPC with differing items, or just item differences within the same UPC code?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Robert Rogerson
Sent: Monday, December 7, 2020 7:49 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Better way to write this query

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.


I have two tables. Both tables have upc and item.

I want to find if for a given upc there is more than one item in either or both tables. So if one table has a upc with two items or both tables have the same upc but different item then I want to return a count > 1.

I believe this query would work.

with t1 as (
select 1 as c1, 2 as c2
from sysibm.sysdummy1 a
union all
select 1 as c1, 2 as c2
from sysibm.sysdummy1 b),

t2 as (
select c1,c2
from t1
group by c1,c2)

select count(*) from t2;

Is there a better/easier way to write this query?

Thanks,

Rob
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com