SQL query with IN or NOT IN - does it have to fail when the list contains a non-existing option?

Dear all,

I’m using the API to compare entries with an external system. The query is like this:

select * from Master WHERE Status NOT IN ('abgeschlossen', 'abgesagt', 'Storniert') AND ANDE IN ('ANDE-98701', 'ANDE-98701', 'ANDE-102701', 'ANDE-102701', 'ANDE-102985', 'ANDE-102985', 'ANDE-108145', 'ANDE-108145', 'ANDE-102714', 'ANDE-102714', 'ANDE-108142', 'ANDE-108142', 'ANDE-106347', 'ANDE-106347', 'ANDE-96364', 'ANDE-96364', 'ANDE-105683', 'ANDE-105683', 'ANDE-106382', 'ANDE-106382') LIMIT 2000

Status in this case is a single select. The integration was working well until someone decided to rename the “Storniert” option of the select to “storniert”. From that moment the query was failing.

When executing the query through the SQL-Plugin in of Seatable, I get the message: “option Storniert does not exist” - but since my automation was previously running fine it took some investigation to understand what’s going on. Also the results array is still present, although empty.

I totally appreciate that SeaTable offers a SQL-like access via the API, but wouldn’t it be better and consistent with SQL to not throw an error when the list contains non-existent items?

For now I can handle it by considering the “success” and “error_message” attributes in the response - so I’m not seeking for help, just wanted to bring it up as a suggestion.

1 Like

We’ll improve this case.

1 Like

great, thank for taking care of this :slight_smile: