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

UserID16644

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

txnelson

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