CODEWITHSUNDEEP
sqloracle-database
Antonio Delicado López
Antonio Delicado López

Reputation: 3

Select numbers where not exist with a specific code

I want to select all numbers where COD = 'GV' but also select the numbers that do not exist with code 'GV'... number 4170 for example...

COD  NUM 
---- ----   
GV   4168    
SERV 4168    
GV   4169    
SERV 4169    
SERV 4170    
SERV 4171    
GV   4171

Upvotes: 0

Views: 31

Answers (2)

MT0
MT0

Reputation: 168351

You can use a partitioned outer join:

WITH bounds ( min_num, max_num ) AS (
  SELECT MIN( num ),
         MAX( num )
  FROM   test_data
)
SELECT t.COD,
       n.NUM
FROM   (
         SELECT min_num + LEVEL - 1 AS num
         FROM   bounds
         CONNECT BY LEVEL <= max_num - min_num + 1
       ) n
       LEFT OUTER JOIN test_data t
       PARTITION BY ( t.COD )
       ON ( n.NUM = t.NUM )
WHERE  t.NUM IS NULL
AND    t.COD = 'GV';

Which, for your sample data:

CREATE TABLE test_data ( COD, NUM ) AS 
SELECT 'GV',   4168 FROM DUAL UNION ALL
SELECT 'SERV', 4168 FROM DUAL UNION ALL
SELECT 'GV',   4169 FROM DUAL UNION ALL
SELECT 'SERV', 4169 FROM DUAL UNION ALL
SELECT 'SERV', 4170 FROM DUAL UNION ALL
SELECT 'SERV', 4171 FROM DUAL UNION ALL
SELECT 'GV',   4171 FROM DUAL;

Outputs:

COD |  NUM
:-- | ---:
GV  | 4170

db<>fiddle here

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143003

Literally, transcribed what you asked for:

-- numbers for COD = GV
select num
from your_table
where cod = 'GV'
union
-- numbers for COD <> GV
select num
from your_table
where cod <> 'GV

Simplified:

select distinct num
from your_table;

as you, basically, want all numbers, regardless of the COD value.

Upvotes: 1

Related Questions