Oracle SQL: Cross Reference Literals

Question

Is there a function or operator or otherwise a simple (r) construct to get the intersection of two tuple literals in the sql oracle?

Considering the following example:

Availability of the following table

------------------------------ TABLE sometable ------------------------------ id | telephone | mobile | fax ------------------------------ 1 | 123 | 456 | 789 

Given a list of n numbers {n1, n2, n3, ..., n} find id, which:

  telephone = n1 or mobile = n1 or fax = n1 or telephone = n2 or mobile = n2 or fax = n2 or telephone = n3 or mobile = n3 or fax = n3 .... or telephone = n or mobile = n or fax = n 

Two plausible solutions:

1. Solution 1

 SELECT id FROM sometable WHERE n1 IN (telephone, mobile, fax) OR n2 IN (telephone, mobile, fax) OR n3 IN (telephone, mobile, fax) .... OR n IN (telephone, mobile, fax) ; 

2. Decision 2

 SELECT id FROM sometable WHERE telephone IN (n1, n2, n3, ..., n) OR mobile IN (n1, n2, n3, ..., n) OR fax IN (n1, n2, n3, ..., n) ; 

However, is there a / operator function to do the following?

 SELECT id FROM sometable WHERE intersect_function ( (telephone, mobile, fax), (n1, n2, n3, ..., n) ) = TRUE ; 

An alternative, simpler design would be appreciated, given that this condition is part of a longer request with more numerous and possibly more complex conditions.

Thanks.

Answer 1

My idea is to convert your search numbers to a table using the with clause:

Then with a little regexp trick, you can create one row per value from one row and map them to your table thanks to the in clause:

 create TABLE sometable ( id number, telephone number, mobile number, fax number ); insert into sometable values(1, 123, 456, 789); insert into sometable values(2, 0, 0, 123); insert into sometable values(3, 456, 0, 0); with w(n) as ( select regexp_substr('123, 456', '\d+', 1, level) n from dual connect by regexp_instr('123, 456', '\d+', 1, level) != 0 ) select * from sometable s, w where wn in (s.telephone, s.mobile, s.fax) ; 

This gives as expected:

 ID TELEPHONE MOBILE FAX N 1 123 456 789 123 2 0 0 123 123 1 123 456 789 456 3 456 0 0 456 

Answer 2

By creating your own type and function, you can achieve something very similar to your desired SQL:

 SELECT id FROM sometable WHERE intersect_function ( num_tab(telephone, mobile, fax), num_tab(123, 456) ) > 0; 

First you need to declare this type:

 SQL> create type num_tab is table of number; 2 / Type created. 

... and this function:

 create or replace function intersect_function ( p_tab1 num_tab , p_tab2 num_tab ) return number is l_intersect num_tab; begin l_intersect := p_tab1 multiset intersect p_tab2; return l_intersect.count; end; 

However, keep in mind that calling the function for each row of data and not being able to use indexes may not be the most efficient solution!