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.

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)
```

Oracle DatabaseSQL

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

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

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

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 ?

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 ?

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.

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
```

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

Thank you, Bill

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

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
Sorry for the delay.. I haven't had time to test this out.

Will this week.

Thank you, Bill

Will this week.

Thank you, Bill

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...

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...

Perfect!

Your help has saved me hundreds of hours of internet surfing.

fblack61

"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,INV

SHIPTO, PRICE, ABS(AMT)) cnt,

sum(AMT) over(partition by Custno,Branchno,Shipto,INV

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,Inv

-- 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 ?