CODEWITHSUNDEEP
sqloracle-database
Abhishek Singh
Abhishek Singh

Reputation: 9745

Minus Query for Numerical column with condition

Suppose I have two tables with Identical column structure.

Table A

Key Value
a 123.452
b 127.851
c 128.478

Table B
Key Value
a   124.46
b   127.578
d   576

Now I want to show all rows in Table A which are not present in table B. But there is also a condition to neglect a difference of +/- 0.01 in value column and consider them as equal.

So the result should be like

Key Value
b 127.851
c 128.478

I know the minus query can be used but it would not take into consideration the additional condition.

Upvotes: 0

Views: 72

Answers (4)

Bikash Ranjan Bhoi
Bikash Ranjan Bhoi

Reputation: 516

Try this

SELECT a.*
  FROM a LEFT OUTER JOIN b ON a.key = b.key
 WHERE (b.key IS NULL OR ABS (a.VALUE - b.VALUE) < 0.01);

For your data b wont be fetched as the difference is more than 0.01

Upvotes: 1

MT0
MT0

Reputation: 167962

Actuall both the data are fetch from same table. I have a date column which will give different set of rows.

You can use NOT EXISTS and compare the values using BETWEEN:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( date_column, key, value ) AS
SELECT DATE '2017-01-01', 'a', 123.452 FROM DUAL UNION ALL
SELECT DATE '2017-01-01', 'b', 127.851 FROM DUAL UNION ALL
SELECT DATE '2017-01-01', 'c', 128.478 FROM DUAL UNION ALL
SELECT DATE '2017-01-02', 'a', 123.46 FROM DUAL UNION ALL
SELECT DATE '2017-01-02', 'b', 127.841 FROM DUAL UNION ALL
SELECT DATE '2017-01-02', 'd', 576 FROM DUAL;

Query 1:

SELECT key, value
FROM   table_name a
WHERE  date_column = DATE '2017-01-01'
AND    NOT EXISTS(
  SELECT 1
  FROM   table_name b
  WHERE  date_column = DATE '2017-01-02'
  AND    a.key = b.key
  AND    a.value BETWEEN b.value - 0.01 AND b.value + 0.01
)

Results:

| KEY |   VALUE |
|-----|---------|
|   c | 128.478 |

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You can use LEFT JOIN on self.

SELECT a.key,a.value FROM 
TableA a LEFT JOIN TableA b
ON a.key = b.key 
AND ABS(a.value - b.value ) <= 0.01
WHERE TRUNC(a.dt) = :date1
AND TRUNC(b.dt) = :date2
AND b.value IS NULL;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

One method is not exists:

select a.*
from a
where not exists (select 1
                  from b
                  where abs(b.value - a.value) <= 0.01
                 );

Upvotes: 1

Related Questions