Avatar of BILL Carlisle
BILL Carlisle
Flag for United States of America asked on

Query return records that have 2 rows that equal zero

Hi All,
The following query returns duplicate (so to speak, positive/negative dups) possibilities..
the price is the same and for a duplicate the AMTs equal zero.
How would I limit the query to only give results of the ones that equal zero?
Right now it could return AMTs that equal each other and would not cancel each other.
select 
"DATA_ID",
"DATA_ID" DATA_ID_DISPLAY,
"CUSTNO",
"STORENO",
"SALEDATE",
"INVOICENO",
"UPC",
"SHIPTO",
SHIPNAME,
"PRICE",
"QTY",
"AMT",
"BRANCHNO",
"ITEMNO"
from (SELECT a.*, count(*) over(partition by Custno,Branchno,Shipto,INVOICENO, SALEDATE, UPC, 
			  SHIPTO, PRICE, ABS(AMT)) cnt
       from "#OWNER#"."MY_DATA" a)
where cnt >1
order by Custno,Branchno,Shipto,Invoiceno,Itemno,PRICE,abs(AMT)

Open in new window

Oracle DatabaseSQL

Avatar of undefined
Last Comment
BILL Carlisle

8/22/2022 - Mon
flow01

select
"DATA_ID",
"DATA_ID" DATA_ID_DISPLAY,
"CUSTNO",
"STORENO",
"SALEDATE",
"INVOICENO",
"UPC",
"SHIPTO",
SHIPNAME,
"PRICE",
"QTY",
"AMT",
"BRANCHNO",
"ITEMNO"
from (SELECT a.*, count(*) over(partition by Custno,Branchno,Shipto,INVOICENO, SALEDATE, UPC,
                    SHIPTO, PRICE, ABS(AMT)) cnt,
sum(AMT) over(partition by Custno,Branchno,Shipto,INVOICENO, SALEDATE, UPC,
                    SHIPTO, PRICE, ABS(AMT)) sumamt
       from "#OWNER#"."MY_DATA" a)
where cnt >1 AND NOT (cnt = 2 and sumamt = 0)  
order by Custno,Branchno,Shipto,Invoiceno,Itemno,PRICE,abs(AMT)

-- but this wil only exclude  a couple of rows in your current query.
-- and somehow i have the idea your are searching for something else
What  rows are you trying to find ?
BILL Carlisle

ASKER
Actually I think AND (cnt = 2 and sumamt = 0)   would do it... I'm looking at a business rule to find these records that cancel each other out and to not show them on the invoice.

Just got done with a conversation.. the rule might even be more tolerant.. to remove also those plus/minus .05 cents of the amount.
Weird I know..

But if that were the case.. how would I do that?

Thanks, Bill
Sharath S

Can you post some sample data and expected result?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
flow01

And tell us the rule,  and don't forget to tell  on what level the rule should be evaluated. ( I doubt if price and abs(amt) should be on your partition clause).
Now
where cnt >1 AND NOT (cnt = 2 and sumamt = 0)  
if there are 3 rows with 1 pair all 3 rows will be shown
where cnt >1 AND NOT (cnt = 2 and sumamt > -0.05 and sumamt <= 0.05)     would  remove the plus/minus .05
but  such a difference cant exist if price is equal and abs(amount) is equal ?
BILL Carlisle

ASKER
535671      535671      161198      083455      02/18/2014      008210494662Yes, you are right, that will not work. The match that has more than 2 rows matching still needs to be available. Look at the 102.6 - 3 rows.. so it adds them. I need the query to exclude one of the positive rows and show the inverse match -102.6 and 102.6
Not sure if possible with SQL.. may have to use PL/SQL after I get the abs rows as below.
234	6656566	FORD #83455	3.95	.08	.33	008	171	0
535674	535674	161198	083455	02/18/2014	008210494662	234	6656566	FORD #83455	3.95	-.08	-.33	008	171	0
538766	538766	161198	083455	02/18/2014	008210494662	657	6656566	FORD #83455	3.4	-.17	-.57	008	182	0
538767	538767	161198	083455	02/18/2014	008210494662	657	6656566	FORD #83455	3.4	.16	.57	008	182	0
538768	538768	161198	013428	02/24/2014	014210554568	211	2112233	FORD #42877	5.13	20	102.6	014	800	102.6
537284	537284	161198	013428	02/24/2014	014210554568	211	2112233	FORD #42877	5.13	-20	-102.6	014	800	102.6
537276	537276	161198	013428	02/24/2014	014210554568	211	2112233	FORD #42877	5.13	20	102.6	014	800	102.6
534146	534146	161198	009026	02/17/2014	017010484328	981	1263329	FORD #02621	1.5	-10	-15	017	925	0
534144	534144	161198	009026	02/17/2014	017010484328	981	1263329	FORD #02621	1.5	10	15	017	925	0

Open in new window

BILL Carlisle

ASKER
The rule is that Custno, Branchno, Shipto, INVOICENO, SALEDATE, UPC, ITEMNO and price all need to be the same and the AMT needs to match but one being positive and the other being negative. I need to show only these pairs.. not a 3rd one..
Thank you, Bill
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
flow01

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
BILL Carlisle

ASKER
Sorry for the delay.. I haven't had time to test this out.
Will this week.
Thank you, Bill
BILL Carlisle

ASKER
Wow flow01!  You are a Guru for sure..

That works great!  I finally got back to this...

All I had to do is add () to the ROW_NUMBER function -- ROW_NUMBER()
and add the ROW_NUMBER() required "Order By" and it worked AWESOME!
Exactly as it should...
BILL Carlisle

ASKER
Perfect!
Your help has saved me hundreds of hours of internet surfing.
fblack61