-2

I have this query in master_t table that hold ration_category column and many number of sectors in sectors column like this 'BN:INS' or 'BN' or 'BN:IM:INS' etc..

select distinct ratio_category d from master_t
where status = 'Y' and (SECTORS = :P23_SECTORS or
(INSTR(':'||:SECTORS ||':',:P23_SECTORS)>0 OR UPPER(:P23_SECTORS) = 'ALL')) order by ratio_category


 P23_SECTORS= 'BN:INS'

 SECTORS='INS:MFI:SB:BN'

: is a separator of multi data

I need to check if data inside P23_sectors are exists in SECTORS variable , but this query doesn't get data because the multi data separator

Is there anyway to adjust the query so I could compare subtext with full text note : the order is different.

Expected output : LL PP CC

Thanks

BODYBOND
  • 117
  • 1
  • 4
  • 12
  • 2
    Hi. Please edit the question and show the table definition and exact sample data and expected output – OldProgrammer Mar 31 '21 at 19:52
  • It appears that your design is fundamentally flawed, violating the first rule of relational data design, aka First Normal Form. A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain. Get your data design right, and your task becomes trivial. – EdStevens Mar 31 '21 at 20:43
  • Please [edit] your question with a [MRE] including: the DDL (`CREATE TABLE`) statement for you tables; the DML (`INSERT`) statements for your sample data that is representative of the problem; a minimal but **COMPLETE** example of your code that we can execute (do not give us a snippet that we cannot execute); and the expected output for your sample data. Please help us to help you by giving us everything we need to be able to answer; if you only give half a question then we either cannot answer or we have to guess and neither is helpful to you. – MT0 Mar 31 '21 at 23:23
  • I will take that into account , Thanks – BODYBOND Apr 01 '21 at 09:34

1 Answers1

1

Here's one option (read comments within code):

SQL> with master_t (ratio_category, sectors) as
  2    -- sample data
  3    (select 1, 'INS:MFI:SB:BN' from dual union all
  4     select 2, 'BN:LF'         from dual
  5    ),
  6  split_t as
  7    -- split SECTORS into rows
  8    (select ratio_category,
  9            sectors,
 10            regexp_substr(sectors, '[^:]+', 1, column_value) sec
 11     from master_t cross join
 12          table(cast(multiset(select level from dual
 13                              connect by level <= regexp_count(sectors, ':') + 1
 14                             ) as sys.odcinumberlist))
 15    ),
 16  split_23 as
 17    -- split P23_SECTORS into rows
 18    (select regexp_substr('&&P23_SECTORS', '[^:]+', 1, level) sec,
 19            regexp_count('&&P23_SECTORS', ':') + 1 cnt
 20     from dual
 21     connect by level <= regexp_count('&&P23_SECTORS', ':') + 1
 22    )
 23  -- return rows that contain complete P23_SECTORS value(s)
 24  select t.ratio_category, t.sectors
 25  from split_t t join split_23 s on s.sec = t.sec
 26  group by t.ratio_category, t.sectors
 27  having count(*) = max(s.cnt);
Enter value for p23_sectors: BN:INS

RATIO_CATEGORY SECTORS
-------------- -------------
             1 INS:MFI:SB:BN

SQL>

Pay attention to comments people posted. They do have serious experience with programming and you'd rather listen to what they say and thank them for spending time to check your problem. Your reply to Ed is rather rude; if I were Ed, I'd make sure not to respond to any of your future questions (read: I'd let you solve your own problems).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57