topic Re: How to skip item on JSL list in SQL query if it does not exist? in Discussions

How to skip item on JSL list in SQL query if it does not exist?

UserID16644 — Thu, 07 Mar 2024 08:05:12 GMT

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

Re: How to skip item on JSL list in SQL query if it does not exist?

txnelson — Thu, 07 Mar 2024 12:14:10 GMT

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?

mmarchandTSI — Thu, 07 Mar 2024 19:41:52 GMT

*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,"','") || "'"