12 nov 2015, om 07:02, J Decker:
> So I've used CTE to solve a simple problem... I'm tempted to use it to
> fix more problems... but I'm wondering how to select different values
> at different levels. I know there's like 'select * from table where
> column in ( set,of,things) ' but can I index into the set? is there
> some sort of way I can specify an array of values?
>
> would like to do something like ' select value from option_map where
> name is ["system","device","com port", "1" ] ' where the program
> statement would look more like
>
> GetOptionValue ( "/system/device/com port/1" ) where the name is
> really sort of registry like and variable in length...
>
> I could probably do some sort of indexing passing that exact string
> through and parsing in SQL the substrings of interest based on the
> level of the query... but would rather preparse the string.
Below is another possible answer. This uses a recursive cte to split
an input full path name into seperate names.
create table map (id, name, parent);
insert into map values
(0, '',''),
(1, 'system', 0),
(2, 'device', 1),
(3, 'com port', 2),
(4, '1', 3),
(5, '2', 3)
;
with walk as (
select 0 as parent, --root
'/system/device/com port/1' as path
union
select id,
substr(path,length(name)+2)
from walk
join map using(parent)
where name = substr(path,2,instr(substr(path,2)||'/','/')-1) --
oef
)
select parent as id from walk
where path='' -- nothing left
;
