CODEWITHSUNDEEP
sqloracle-database
Brian Brock
Brian Brock

Reputation: 351

Select records based on variable known value

I need to select a series of records based on a known value. The record IDs follow a hierarchical structure where the first 4 characters are constant (IA09), followed by 4 digits representing an organization, followed by 4 characters representing an entity within the parent organization. The goal is to select all "child" records of the known value, as well as the "known value" record.

Sample Data Set:

IA0900000000
IA0912340000
IA0912340109
IA0912340418
IA0912340801
IA0945810000
IA0945810215
IA0945810427
IA0945810454

Here is the same dataset, indented to illustrate the hierarchical structure.

IA0900000000
  IA0912340000
    IA0912340109
    IA0912340418
    IA0912340801
  IA0945810000
    IA0945810215
    IA0945810427
    IA0945810454

Example 1
If the known value is 'IA0900000000', I need to select all records in the dataset.

Example 2
If the known value is 'IA0945810000', I need to select all records that begin with 'IA094581'

Example 3
If the known value is 'IA0912340109', I need to select ONLY the record with that ID as it has no child records.

The actual dataset is quite larger than this sample, and the known value will be different for each user of the database.

Is there a simple comparison I could employ in the WHERE clause that will give me the correct subset of records?

Upvotes: 0

Views: 43

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

You can use simple "like" in your case:

with t(org) as (-- test_data:
select 'IA0900000000' from dual union all
select 'IA0912340000' from dual union all
select 'IA0912340109' from dual union all
select 'IA0912340418' from dual union all
select 'IA0912340801' from dual union all
select 'IA0945810000' from dual union all
select 'IA0945810215' from dual union all
select 'IA0945810427' from dual union all
select 'IA0945810454' from dual 
)
select 
  regexp_replace(
     regexp_replace(
        regexp_replace(t.org,'0{4}')
       ,'0{4}')
    ,'(.{4})'
    ,'\1.'
  ) as short_org_path -- just for better readability
 ,t.* 
from t
where t.org like regexp_replace(regexp_replace('&input_org','0{4}'),'0{4}')||'%'
/

Upvotes: 0

Gabriel Durac
Gabriel Durac

Reputation: 2760

Assuming your table is called YourTable and the column name is column. You could remove the trailing 0 from your search term and concatenate it with a wildcard (%) and use the LIKE operator like so:

SELECT * 
FROM YourTable 
WHERE column LIKE TRIM(TRAILING '0' FROM  'IA0945810000') || '%'

Upvotes: 1

Related Questions