Oracle DB : Like with multiple values

Question

This is my query

SELECT dia
FROM CRES
WHERE pro_id = 2
AND 8103434563 LIKE
  ( SELECT dial_pattern||'%' FROM CDIVN WHERE dial_id = 1
  );

Now select dial_pattern||'%' from CDIVN where dial_id = 1 can give multiple results. Hence, my main query is failing and reason is "sub query returns more than one row". This is because i have mentioned like.

but my logic requires like because i want 8103434563 with pattern match condition from table CDIVN.

How do i modify my query. Please help.

=======

 CREATE TABLE CDIVN
   (    DIAL_PATTERN_ID NUMBER NOT NULL ENABLE, 
    DIAL_PATTERN VARCHAR2(30 BYTE) NOT NULL ENABLE, 
    OTHERS VARCHAR2(64 BYTE), 
     CONSTRAINT "CDIVN_PK" PRIMARY KEY ("DIAL_PATTERN_ID", "DIAL_PATTERN")

   );


Insert into CDIVN (DIAL_PATTERN_ID,DIAL_PATTERN,OTHERS) values (1,'810','abc');
Insert into CDIVN (DIAL_PATTERN_ID,DIAL_PATTERN,OTHERS) values (1,'811','xyz');
Insert into CDIVN (DIAL_PATTERN_ID,DIAL_PATTERN,OTHERS) values (1,'812','aaa');
Insert into CDIVN (DIAL_PATTERN_ID,DIAL_PATTERN,OTHERS) values (5,'999','www');
Insert into CDIVN (DIAL_PATTERN_ID,DIAL_PATTERN,OTHERS) values (9,'333','ewe');


  CREATE TABLE CRES
   (    PROFILE_ID NUMBER NOT NULL ENABLE, 
    PROFILE_NAME VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    DIALLED_PATTERN VARCHAR2(15 BYTE), 
     CONSTRAINT "CRES_PK" PRIMARY KEY ("PROFILE_ID")

   ) ;


Insert into CRES (PROFILE_ID,PROFILE_NAME,DIALLED_PATTERN) values (1,'A','1');
Insert into CRES (PROFILE_ID,PROFILE_NAME,DIALLED_PATTERN) values (2,'B','5');
Insert into CRES (PROFILE_ID,PROFILE_NAME,DIALLED_PATTERN) values (3,'C','9');

I have CRES-profile id and one number with me from other sources and these are 1 and 81034345 respectively.

Now select DIALLED_PATTERN from CRES where PROFILE_ID=1;

This will give me DIALLED_PATTERN as 1.

Now select DIAL_PATTERN from CDIVN where DIALLED_PATTERN_ID = 1 ( DIALLED_PATTERN )

This will give DIAL_PATTERN as 810, 811, 812.

Now if 81034345 is matching with any one 810% or 811% or 812%. then i need 810% as my answer.

What is the relationship between CRES and CDIVN tables to join? — Lalit Kumar B
– Lalit Kumar B, Commented Nov 25, 2015 at 7:21
dial_id. CRES dial_id is foreign key of CDIVN dial_id. SELECT dial_pattern||'%' FROM CDIVN WHERE dial_id = 1, here 1 is CRES-dial_id — VJS
– VJS, Commented Nov 25, 2015 at 7:23
Post the create and insert statements and show your desired output. At least create a SQL Fiddle — Lalit Kumar B
– Lalit Kumar B, Commented Nov 25, 2015 at 8:27

t1t1an0 · Accepted Answer · 2015-11-25 07:28:17Z

1

if i may say so, i also had this crazy obsession for sql to have an IN LIKE functionality. and to do this, instead of putting the subquery in your criteria, you may write it as a JOIN on table CRES.

the way i see this is that the subquery

( SELECT dial_pattern||'%' FROM CDIVN WHERE dial_id = 1
  );

in the criteria does not directly affect the rows from table CRES but is being used as a filter for all the results. meaning, if the subquery does not return (a) value(s), the whole query should not return anything as well.

edited Nov 25, 2015 at 7:28

answered Nov 25, 2015 at 7:21

t1t1an0

2811 silver badge16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VJS Over a year ago

dial_id. CRES dial_id is foreign key of CDIVN dial_id. SELECT dial_pattern||'%' FROM CDIVN WHERE dial_id = 1, here 1 is CRES-dial_id . Can you suggest how to write

Frank Ockenfuss · Accepted Answer · 2015-11-25 11:16:24Z

Collect the like-values and build a regexp-expression, e.g.

-- Test Data
with CDIVN as
 (select 1 as dial_id, 'ABC' as val
    from dual
  union all
  select 1 as dial_id, 'ACD' as val
    from dual
  union all
  select 1 as dial_id, 'XXA' as val from dual),

CRES as
 (select 2 as proc_id, 'ABCD' as val
    from dual
  union all
  select 2 as proc_id, 'DABCD' as val
    from dual
  union all
  select 2 as proc_id, 'ACF' as val
    from dual
  union all
  select 2 as proc_id, 'XXAF' as val from dual)

,
-- Build regexp expression: 1, 'ABC|ACD|XXA'
CDIVN_PATTERN as
 (select dial_id,
         listagg(val, '|') within group(order by dial_id) as val_pattern
    from CDIVN
   group by dial_id)

-- Use this expression by regexp_like
select *
  from CRES c
 where regexp_like(c.val,
                   (select '^' || p.val_pattern
                      from cdivn_pattern p
                     where p.dial_id = 1));

Lalit Kumar B · Accepted Answer · 2015-11-25 10:23:04Z

0

You are going in right direction, just that you need to JOIN the two tables, and no need of sub-query, you could directly use LIKE dial_pattern||'%'.

SQL> SELECT a.*, b.*
  2  FROM CRES A
  3  JOIN CDIVN b
  4  ON (b.DIAL_PATTERN_ID = A.profile_id)
  5  WHERE TO_CHAR(8103434563) LIKE dial_pattern||'%';

PROFILE_ID PROFILE_NAME DIALLED_PATTERN DIAL_PATTERN_ID DIAL_PATTER OTHERS
---------- ------------ --------------- --------------- ----------- ------
         1 A            1                             1 810         abc

edited Nov 25, 2015 at 10:23

answered Nov 25, 2015 at 7:27

Lalit Kumar B

49.5k13 gold badges105 silver badges132 bronze badges

14 Comments

VJS Over a year ago

but in CDIVN table, i have multiple dial patterns ie 810, 812, 813 with dial_is as 1. now if 8103434563 matches with anyone ( 810, 812, 813 ) it should return. here 8103434563 matches with 810

Lalit Kumar B Over a year ago

Are you OK if you get all rows where dial_pattern starts with 8? Or you want only three patterns 810, 812 and 813? Is 81% ok for you? That would give you all dial_patterns that starts with 81.

VJS Over a year ago

in CDIVN table, i have multiple dial patterns ie 810, 812, 813 with dial_is as 1. Now 8103434563 like ( 810%, 812%, 813% ), then its mathing with 810% then it should return value

VJS Over a year ago

should not be b.dial_pattern LIKE %8103434563% but i need 8103434563 like ( pattern match from CDIVN for ex 810%, 812%, 813% )

Lalit Kumar B Over a year ago

@VJS Ok, see the updated query. Please mark it as answered, would help others too!.

|

David Aldridge · Accepted Answer · 2015-11-25 09:31:35Z

0

If I understand correctly, I would go with:

SELECT dia
FROM   CRES
WHERE  pro_id = 2
AND    exists (
         SELECT null
         FROM   CDIVN
         WHERE  dial_id = 1 and
                TO_CHAR(8103434563) like dial_pattern||'%');

answered Nov 25, 2015 at 9:31

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

1 Comment

VJS Over a year ago

No. i want something like this...logically...i know this query is wrong but logically i need this select DIALLED_PATTERN from CRES where PROFILE_ID=1 and 81032144 like ( select DIAL_PATTERN||'%' from CDIVN where dial_pattern_id = 1 );

Collectives™ on Stack Overflow

Oracle DB : Like with multiple values

4 Answers 4

1 Comment

Comments

14 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

14 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related