CASE WHEN THEN ELSE END SQL

[ashmita_g]

[ FlexSim 20.1.1 ]

Hi,

I have been trying to implement "Case when then else end" statement in flexsim but I think I am getting the syntax wrong. Could you please guide?

int entry = Table.query("Select label_name from ListName CASE WHEN label_name =$1THEN label_name END", token.label_name)[1][1];

In the above query, I want to check the list (ListName) for the presence of a particular part which matches with the information being carried by the token's label (label_name).

It may so happen that the list may not have a part which matches with the label of the token. In such a case it needs to return a NULL value. Assuming like in SQL, when else is not mentioned in the query it return a NULL value I have left it from this query as well.

[mischa_spelt · Accepted solution]

I'm not sure what you expected FlexSim to do here, because that's not the way you use CASE WHEN, even in regular SQL.

But it sounds like what you want to do is really just a regular SELECT:

Table result = Table.query("SELECT 1 FROM ListName WHERE label_name = $1", token.label_name);

and then check if there are any rows in the result set (result.numRows > 0).

[ashmita_g]

Hi @Mischa Spelt,

Thanks for the reply. Yes I had used this query as you mentioned, previously.

And yes this is my exact requirement based on my problem.

However I noticed the system console throwing an error. This error occurred every time a token, having a label_name which did not match with the label present in the list ran through the code.

The error read as follows:

Flexscript Exception: Invalid Row number : 1 in Table.query at resultTable.

To the best of my understanding I think because there are no matching entries, the console throws that error. Hence I wanted to return a value when a label_name that does not match the List entries is encountered. Therefore I opted for CASE WHEN, which I think is the correct sql way when you need to return a value for the occurrence of a particular case. I assumed if I could return a null value I could fix the issue.

I have attached a sample model demonstrating the issue. In that the source creates 2 integer labels, namely '1' and '2', under the label 'Type'. The process flow has a custom code which checks for labels '1', '2' and '3' in the said list. You can find the exact error showing when a token label that does not match the label in the list is encountered.

This is my doubt, because I will not be able to avoid such a circumstance. How can I remove that error.

Query_error.fsm

Thanks in advance.

[moehlmann_fe]

Your code assumes that the query will always return at least one row. As @Mischa Spelt said, to circumvent the error you should first check wether the result has any rows and only then assign the value.

int sumlabel = 0;
if(n>0)
{    
Table QueryResult = Table.query("SELECT Type FROM ItemList1 WHERE Type = $1", token.Type);    
if(QueryResult.numRows > 0)    
{        
   sumlabel = QueryResult[1][1];    
}    
List("ItemList2").push(token);
}

[ashmita_g]

Hi @Felix Möhlmann, thanks for the explanation. Really helped and gave clarity to the doubt. Thanks again, really appreciate it.

[Jeanette_Fullmer]

Hello @Ashmita G,

Did Mischa and Felix answer your question? If so, can you accept the answer?