BILL Carlisle

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

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

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?

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 ?

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.

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

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

Thank you, Bill

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

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

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

ASKER

Perfect!

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