topic Re: SQL query using the Label in FlexSim Forum

SQL query using the Label

selvantamil674 — Mon, 27 May 2024 11:37:31 GMT

[ FlexSim 23.1.1 ]

I am trying to query a GlobalTable1 using SQL, but the column name is present in token.col in the token, how to access the token.col value in the sql query

Re: SQL query using the Label

moehlmann_fe — Mon, 27 May 2024 11:46:22 GMT

Concatenate the label value and the query string.

"SELECT [" + token.col + "] FROM tableName WHERE..."

Re: SQL query using the Label

selvantamil674 — Mon, 27 May 2024 11:54:20 GMT

Can you please help me out with the example

Re: SQL query using the Label

moehlmann_fe — Mon, 27 May 2024 12:37:48 GMT

What is unclear about the example I provided? You type your query as you would normally until you would enter the column name. You then end the string, add the column name to it with ' + token.col', then you add the the rest of the query as a string again ( + "...").

Re: SQL query using the Label

selvantamil674 — Tue, 28 May 2024 06:11:17 GMT

Yeah it working fine Thanks for the Answer, Now, I just need to Query two column in WHERE which matches the value, can you please help out in this, i am attaching the file with thisFetch_Table_using_Token_and_SQL.fsm

Re: SQL query using the Label

moehlmann_fe — Tue, 28 May 2024 07:25:41 GMT

- The label on the token is called "Name", not "Col"

- When comparing a column to a string value, the string needs to be enclosed in single quotation marks within the query (WHERE [Name] == 'A'), so you need to add those to the query string in front of and after the label value.

- You need to convert the number value from "token.Num" to a string before you can insert it into the query like this (string.fromNum(token.Num))

- You only write WHERE once, then write multiple checks separated by AND or OR

Table result = Table.query("SELECT * FROM Test1 \
    WHERE [Name] = '"+token.Name+"' AND [Num] = "+string.fromNum(token.Num)+"");

With all that said, values that you want to compare columns to can more easily be passed in via the $-syntax. Inserting the label directly into the string is only needed if it represents a column name or SQL clause.

Table result = Table.query("SELECT * FROM Test1 WHERE [Name] = $1 AND [Num] = $2", token.Name, token.Num);

When the query is run, $1 will be replaced by the first value that is passed in after the query, $2 by the second and so on.

Re: SQL query using the Label

selvantamil674 — Tue, 28 May 2024 07:52:17 GMT

Thanks for the Answer