CODEWITHSUNDEEP
sqloracle-database
Chris Pop
Chris Pop

Reputation: 27

Extract specific values from the string and join with other table

I have a requirement in which I have two tables one stores condition and the second stores lookup values.

For eg.

Table 1 (Condition Table):

Condition
"100000010073024" = "BILLED"
"100000010073027" = "Not Billed"
"100000010073026" = "Not Billed" Or "100000010073055" = "Billed"

Table2(Lookup Values):

Lookup Id Meaning
100000010073024 Test
100000010073027 Test1
100000010073026 Test2
100000010073055 Test3

So I want results like:

Result
Test = "BILLED"
Test1 = "Not Billed"
Test2 = "Not Billed" Or Test3 = "Billed"

So how can I achieve this through oracle sql?

Upvotes: 0

Views: 249

Answers (2)

MT0
MT0

Reputation: 167982

The simplest method may be to refactor your conditions table to have each lookup id/term pair in a separate row:

CREATE TABLE conditions (id, lookup_id, term) AS
SELECT 1, 100000010073024, 'BILLED'     FROM DUAL UNION ALL
SELECT 2, 100000010073027, 'Not Billed' FROM DUAL UNION ALL
SELECT 3, 100000010073026, 'Not Billed' FROM DUAL UNION ALL
SELECT 3, 100000010073055, 'Billed'     FROM DUAL;

Then you could also add referential constraints to the lookups table.

ALTER TABLE conditions ADD CONSTRAINT conditions__lookup_id__fk 
  FOREIGN KEY (lookup_id) REFERENCES lookups(lookup_id);

The query is simply:

SELECT LISTAGG('"' || l.meaning || '" = "' || c.term || '"', ' OR ')
         WITHIN GROUP (ORDER BY ROWNUM) AS result
FROM   conditions c
       INNER JOIN lookups l
       ON (c.lookup_id = l.lookup_id)
GROUP BY c.id;

Which outputs:

RESULT
"Test" = "BILLED"
"Test1" = "Not Billed"
"Test2" = "Not Billed" OR "Test3" = "Billed"

db<>fiddle here

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

Here's one option.

Based on sample data you posted:

SQL> with
  2  t1 (condition) as
  3    (select '"100000010073024" = "BILLED"' from dual union all
  4     select '"100000010073027" = "Not Billed"' from dual union all
  5     select '"100000010073026" = "Not Billed" Or "100000010073055" = "Billed"' from dual
  6    ),
  7  t2 (lookup_id, meaning) as
  8    (select 100000010073024, 'Test'  from dual union all
  9     select 100000010073027, 'Test1' from dual union all
 10     select 100000010073026, 'Test2' from dual union all
 11     select 100000010073055, 'Test3' from dual
 12    ),
 13  --

Split conditions to rows (separated by "or") so that you could (in the final select) work on each of them, separately:

 14  -- split conditions to rows
 15  splcon as
 16    (select trim(regexp_substr(replace(upper(condition), 'OR', '#'), '[^#)]+', 1, column_value)) condition
 17     from t1 cross join
 18       table(cast(multiset(select level from dual
 19                           connect by level <= regexp_count(upper(condition), 'OR') + 1
 20                          ) as sys.odcinumberlist))
 21    )

Join condition is made by instr function (whether condition contains lookup_id or not):

 22  select
 23    b.meaning ||
 24    replace(substr(s.condition, instr(s.condition, '=') - 1), '"', '') as result
 25  from splcon s join t2 b on instr(s.condition, b.lookup_id) > 0;

RESULT
------------------------------
Test = BILLED
Test1 = NOT BILLED
Test2 = NOT BILLED
Test3 = BILLED

SQL>

Upvotes: 1

Related Questions