Written by

Senior Cloud Architect at InterSystems

STAFF

Question Eduard Lebedyuk · Sep 8, 2019

SQL condition in SELECT query

I'm writing "Finder"-like dialog and need to get list of BPL classes.

The query is dependent on current "path" - it outputs all BPLs in the current path (if any) and all sub-packages which contains BPLs.

So far I have this query:

Query getBPL(path, needPlus = {(path'="")}) As %SQLQuery
{
SELECT DISTINCT $PIECE(Name, '.', 1, $LENGTH(:path, '.') + :needPlus) Name
FROM %Dictionary.ClassDefinitionQuery_SubclassOf('Ens.BusinessProcessBPL')
WHERE Name %STARTSWITH :path
}

My problem is, if I'm not in "root" path I need to add additional dot level, but in root path I only need to add one.

Currently I'm doing it with needPlus autocalculated parameter, but is there a way to remove it?

Discussion (2)0

Add reply

Comments

Benjamin De Boe · Sep 9, 2019

how about CASE :path WHEN '' THEN 1 ELSE $LENGTH(:path,'.')+1 END

0 0

Eduard Lebedyuk Sep 9, 2019 to Benjamin De Boe

Thank you. That's it.

I wanted to move calculation into $PIECE which was the root of my troubles.

Interestingly, when I pass empty path value ("") it is recognized as NULL on SQL side and

CASE :path
WHEN NULL

does not work (never gets hit probably because it compares using equals and not is).

So it's either:

SELECT DISTINCT
    CASE nvl(:path,'')
    WHEN '' THEN $PIECE(Name, '.')
    ELSE $PIECE(Name, '.', 1, $LENGTH(:path, '.') + 1) END Name
FROM %Dictionary.ClassDefinitionQuery_SubclassOf('Ens.BusinessProcessBPL')
WHERE Name %STARTSWITH :path

or:

SELECT DISTINCT
    CASE
    WHEN :path IS NULL THEN $PIECE(Name, '.')
    ELSE $PIECE(Name, '.', 1, $LENGTH(:path, '.') + 1) END Name
FROM %Dictionary.ClassDefinitionQuery_SubclassOf('Ens.BusinessProcessBPL')
WHERE Name %STARTSWITH :path

It raises the question of how to pass empty string to SQL and avoid it being recognized as NULL, but it's irrelevant for my original inquiry.

0 0