Query: join table to model

Question

question

Mischa Spelt asked Jul 4, '16 anthony.johnson edited Jul 6, '16

Query: join table to model

I found some strange behavior when joining a table to the model() in a query.

Suppose I have a global table with values, and objects in my model that have those values on a specific label. What I would like to do is join the table to the objects in the model, so as to find the objects with the label with the matching value.

Please see the attached model join-table-to-model.fsm for my attempts.

First I tried option A:

query( "SELECT [Objects].[ID], $2 AS Object FROM Objects, $1 AS Model",
  model(), $iter( 1 ), getlabel( $iter( 1 ), "id" ) );

Strangely, the references I get in the second column point to the rows of my Objects table. It seems like $iter does not loop through $1, which is model(), as documented, but rather through the rows of the first source, which is the global table Objects. However, this is not quite right, as changing $iter(1) to $iter(2) does not fix the problem - I only get references to /Tools instead.

Just for fun, I tired option B, where all I did was switch the sources so model() becomes the first source:

query( "SELECT [Objects].[ID], $2 AS Object, $3 AS ObjectLabel FROM $1 AS Model, Objects",
  model(), $iter( 1 ), getlabel( $iter( 1 ), "id" ) );

This already looks better: I get the Cartesian product of the rows in my table and the objects in my model, with the correct label value in the third column (where set).

So I thought I was done: only need to make it into a proper join by adding a WHERE clause:

query( "SELECT [Objects].[ID], $2 AS Object, $3 AS ObjectLabel FROM $1 AS Model, Objects WHERE $3 = [Objects].[ID]",
	model(), $iter( 1 ), getlabel( $iter( 1 ), "id" ) );

But suddenly I get only one match! It seems like the $3 in the WHERE clause is not evaluated properly, because when I use its alias:

query( "SELECT [Objects].[ID], $2 AS Object, $3 AS ObjectLabel FROM $1 AS Model, Objects WHERE ObjectLabel = [Objects].[ID]",
	model(), $iter( 1 ), getlabel( $iter( 1 ), "id" ) );

I get the expected result.

Finally, in my actual model I ran into a problem when I dumped the query to a bundle instead of a table: it made all the fields numeric so all my string labels turned into zeroes.

Software Version:

FlexSim 16.1.0

query development

join-table-to-model.fsm (15.1 KiB)

Answer 1 · 2016-07-05T21:40:12Z

Sam Stubbs answered Jul 5, '16 Mischa Spelt commented Jul 6, '16

From what I understand, when you use the '$' notation and give it an alias, it needs to be consistent. So if you give it an alias, you need to consistently use the alias name. Why it's this way I'm not completely sure. But just looking at your test example, it seems to work when you are consistent. (ie if I say "$3 AS ObjectLabel" then I need to reference it as ObjectLabel later on as well. If I DON'T give it the alias ObjectLabel,, then it seems to accept the $3 notation later on.)

· 1

Mischa Spelt commented · Jul 06, 2016 at 12:09 PM

Thanks, that's a good observation!

0 ·

Answer 2 · 2016-07-06T15:47:20Z

anthony.johnson answered Jul 6, '16 anthony.johnson edited Jul 6, '16

I think sometimes when you do an inner join without explicitly defining what table a $ column belongs to, it may get mixed up. You might try putting Model.$2 instead of just $2 in the SELECT clause. The other thing is, there is a bug on inner joins and using $1 as a table (which will be fixed in the next bug fix release). So try swapping $1 and $2, as well as their parameters.

question