How to skip item on JSL list in SQL query if it does not exist?Hi all,
I have a script that queries from JSL list. However, it gets an error whenever something is not existing on the database. Is there a way that I can skip what is not existing and proceed to the next value in the list?
I have this example:
NameList = {"John","Jane","Anna","Dominic"};
NameItems = ConcatItems(NameList ,",");
Eval (Parse (Evalinsert("\[
db= Open Database("Server Specs", "SELECT * FROM dbNames WHERE name IN (^NameItems ^)")
]\")));;So for example, John doesn't exist, it should skip and go with Jane, Anna or Dominic
Automation and ScriptingWindowsRe: How to skip item on JSL list in SQL query if it does not exist?An IN operator should not fail unless none of the values in the list are found. You should be able to protect this issue with a Try() function
Eval (Parse (Evalinsert("\[
db= Try( Open Database("Server Specs", "SELECT * FROM dbNames WHERE name IN (^NameItems ^)") )
]\")));
Also, it is my experience that your IN operator should enclose the character elements within the call with quotes.
WHERE name IN ('John','Jane','Anna','Dominic')
Changing your JSL to the below will change the IN operator call to the above form
NameList = {"John","Jane","Anna","Dominic"};
NameItems = "'" || ConcatItems(NameList ,"','") || "'";Re: How to skip item on JSL list in SQL query if it does not exist?*edit
Didn't see Jim's response. Good response, though.
It doesn't look like the list of names is formatted properly.
John,Jane,Anna,Dominic
Should be 'John','Jane','Anna','Dominic'
If you want to use Concat Items(), you'll need to do something like this:
NameItems = "'" || Concat Items(NameList,"','") || "'"
