op 12-11-2015 17:35 schreef J Decker op d3ck0r at gmail.com: > 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. ... >> with walk as ( ... >> > > 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 > So something like "select value from option_map_view where path is <array or set type>"? A path name like '/system/device/com port/1' is used as an array of names here. Only the indexing with intst and substr is laborious. Maybe some future SQLite version includes a group_split function to make this easier. It seems impossible to me in plain SQL to write an efficient view for a query like this. As you say below the whole map need to be walked at each query to find a match. User defined functions, virtual tables or function based indexes may offer an efficient solution.
> would be better than building up the address to get a comparison at > the end since the whole map would have to be walked. >
