Hi, Currently we have a query as given below,
select count (distinct chk_ppl_val.serialnum) serialnum__ndv, count (distinct chk_ppl_val.individual_id) individual_id__ndv from chk_ppl_val where
(substring(industry_code,1,4) in (SELECT distinct string_value FROM data_upload where id = '5379e360b0c8f9de684'))
Above query pull records, if the first 4 digit of industry code matches string_value in the data_upload table. Now instead of first 4 digit matching, we need to change above to query to be driven based on data in the string_value field. If the string_value contains 2 digit it industry code should use that 2 digit to pull like matches, likewise if it has 3,4,6 digits it should pull corresponding like matches in industry_code.
Please guide me on how to implement it. Whether we can have like clause with IN operator. Below are data example.
Legends: IC - Industry code, SV - String_value, CO - current output, EO- Expected output
IC SV CO EO
3145 31 123652 3145
31801 1236 123794 31801
450 45 12984 450
12366 678 12366 12366
923412 98741 123652
31453 1298 123794
123652 123783 12984
123794 987412
12984 678123
987412
678123
If you run the following query
select distinct rowid rid
from chk_ppl_val c,
data_upload d
where d.id = '5379e360b0c8f9de684'
and c.industry_code like d.string_value||'%'
you get all the rows in chk_ppl_val where the industry code matches. We need 'distinct' because the join might return the same rows multiple times. Once I have these, we can simply do:
select *
from chk_ppl_val
where rowid in (
select distinct rowid rid
from chk_ppl_val c,
data_upload d
where d.id = '5379e360b0c8f9de684'
and c.industry_code like d.string_value||'%'
)
You got do it like this:
select distinct c.col1, c.col2, c.col3, ......
from chk_ppl_val c,
data_upload d
where d.id = '5379e360b0c8f9de684'
and c.industry_code like d.string_value||'%'
but that might mean some heavy sorting/temporary segment work, or problems depending on the data types of the columns.