How to create a subquery in the query?

[kamil_ks]

[ FlexSim 19.0.7 ]

Hello,

I have written the SQL code below. It works fine but it needs to copy the Table Data to Global Table test using cloneTo().

Object buffer = model.find(token.ZalAdr);
Table Data = Table.query("SELECT Bin, ROW_NUMBER() AS NrRow FROM $1 AS Distance",
						buffer.labels["Default"]);	
Data.cloneTo("test");
token.SKU = string.fromNum(token.SKU);		
Table Result = Table.query("SELECT * FROM test AS R, Biny AS B \
							WHERE \
							SKU = $1.SKU \
							AND R.Bin = B.Bin \
							ORDER BY \
							Data ASC, Field ASC, NrRow ASC", token);
token.BinName = Result[1][1];

1. Is it possible to create a subquery (Table Data) using a $ alias in the Table result?

Something like this (not working):

Object buffer = model.find(token.ZalAdr);
token.SKU = string.fromNum(token.SKU);
Table Result = Table.query("SELECT * FROM $1.query("SELECT Bin, ROW_NUMBER() AS NrRow FROM $1 AS Distance", buffer.labels["Default"]) AS R, Biny AS B \
WHERE \
SKU = $2.SKU \
AND R.Bin = B.Bin \
ORDER BY \
Data ASC, Field ASC, NrRow ASC", Table, token);
token.BinName = Result[1][1];

2. Is it possible to refer directly to the Table Data without using cloneTo()?

Unfortuanately, I am not allowed to publish the simulation model.

[JordanLJohnson · Accepted solution]

1. It sounds like you are asking about a nested query. You can write query code like this:

SELECT ColA, ColB FROM (
	SELECT ColC AS ColA, ColD AS ColB FROM GlobalTable1
) ORDER BY ColA

2. In a query, you can't query a current query result. But I think the nested query will solve your problem.

[kamil_ks]

Thank you very mych for your help, I am also struggling with this example:

Table Result2 = Table.query("SELECT Bin, Rotacja, Content FROM Biny \
			WHERE \
			[Rotacja] == $1.rotacja \
			ORDER BY CONTENT \
			CASE Rotacja WHEN 'A' THEN DESC \
			ELSE ASC END", token);

Is it possible to create such a CASE statement in Flexsim?