CASE WHEN THEN ELSE END SQL

Question

question

Ashmita G asked Apr 20, '21 Jeanette F commented Apr 21, '21

CASE WHEN THEN ELSE END SQL

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.

Software Version:

FlexSim 20.1.1

flexscript flexsim 20.1.1 sql queries case when then else end

Answer 1 · 2021-04-20T12:26:46Z

Mischa Spelt answered Apr 20, '21 Jeanette F commented Apr 21, '21

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).

· 4

Ashmita G commented · Apr 20, 2021 at 03:51 PM

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.

0 ·

query-error.fsm (31.0 KiB)

Felix Möhlmann Ashmita G commented · Apr 21, 2021 at 10:16 AM

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);
}

2 ·

Ashmita G Felix Möhlmann commented · Apr 21, 2021 at 08:07 PM

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

0 ·

Show more comments

question