On 2015/11/12 8:02 AM, J Decker wrote: > 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.
I'm not sure exactly what you fancy happening here, specifically what you mean by "levels"... Selecting from an array-like list is not hard using a cte (or sub-query), this kind of thing will work fine: WITH NMT(Idx,Name) AS ( SELECT 0, 'system' UNION ALL SELECT 1, 'localhost' UNION ALL SELECT 2, 'Andy-PC' ), PRT(Idx,Port) AS ( SELECT 0, 'COM1' UNION ALL SELECT 1, 'COM3' UNION ALL SELECT 2, 'COM4' ) SELECT '/' || NMT.Name || '/' || PRT.Port || '/' || A.ID FROM NMT, PRT, (SELECT 1 AS ID) AS A WHERE NMT.Idx=0 AND PRT.Idx=1; which should yield: /system/COM3/1 etc. This example makes a lot of assumptions about what you meant and may be totally the wrong direction - some clearer question will greatly help us. Thanks! Ryan
