On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma <pasma10 at concepts.nl> wrote:
> 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, '',''), -- level 0
> (1, 'system', 0), -- level 1
> (2, 'device', 1), -- level 2
> (3, 'com port', 2), -- level 3
> (4, '1', 3), -- level 4
> (5, '2', 3) -- level 4
> ;
> with walk as (
> select 0 as parent, --root
> '/system/device/com port/1' as path ,
0 as level
> union
> select id,
> substr(path,length(name)+2) ,
level+1 as level
> 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
> ;
(something like that....)
was hoping to not have to do the substr part in the query....
and would like the path to be more on the external usage of 'walk' in
this case than inside the expression
would be better than building up the address to get a comparison at
the end since the whole map would have to be walked.
TO answer other's questions; 'level' refers to the dept of the tree
searched... level indiciators added as comments to the insert above...
