sql functions

Hello,

I am playing around with the sql functionality in FlexSim 7 and I like it a lot. But will the functionality be extended? (or maybe I am doing something wrong)

I could really use the functionality of 'Group By' and being able to multiply different columns using '*' between multiple columns instead of ','. Furthermore I would like to be able to select columns with part of a string in the text. For this last feature I can run a query without getting any errors, but don't get any results.

Someone already more luck with these things, or are they really not there (yet).

Esther

Hey Esther,
Right now, GROUP BY and LIKE aren't supported. We put the LIKE syntax into the parser, but didn't have time to actually implement it, which is why you are not getting any errors but it's not doing anything. As far as multiplying different columns, that should work fine. I tested the following:
SELECT * FROM Boxes WHERE Length * Width * Height > 5

on Boxes table:

Name, Length, Width, Height
Box1      1          1        2
Box2      2          2        2
Box3      3          2        1

And my result was Box2 and Box3. Maybe I'm missing what you're trying to do.

As a workaround for the LIKE, you can do a completely manual query using the flexsim-specific $ syntax, as follows:

query("SELECT $3 AS Name, $4 AS ID FROM $1 WHERE $2", 
	/*$1*/reftable("Shapes"), 
	/*$2*/stringsearch(gets(rank($iter(1), 1)), "Box", 0) >= 0, 
	/*$3*/gets(rank($iter(1), 1)),
	/*$4*/get(rank($iter(1), 2))
);

Given the Shapes table,

Name,     ID
BoxA,      1
SphereA,   2
BoxB,      3

This will return the BoxA and BoxB rows.

Hello Anthony,

Thanks a lot for the answers. With the multiplying I was trying to get a result so trying to get Length * Width * Height AS Volume, so that is maybe why I got an error.

The rest I will check on Monday, have a nice weekend.

Esther

Hello Anthony,

Playing around with the sql code again, but is it possible to use the $iter(1) for a databundle? I can get it to work with a table, but not with a databundle. Because we have a lot of data stored, I would prefer to use databundles to keep the file size as low as possible.

Esther

To do the same query as above except on bundles, change it to the following:

query("SELECT $3 AS Name, $4 AS ID FROM $1 WHERE $2", 
	/*$1*/getbundlenrentries(bundleNode), 
	/*$2*/stringsearch(getbundlevalue(bundleNode, $iter(1) - 1, 0), "Box", 0) >= 0, 
	/*$3*/getbundlevalue(bundleNode, $iter(1) - 1, 0),
	/*$4*/getbundlevalue(bundleNode, $iter(1) - 1, 1))
);

Note that in the original table-based example, $1 returned a reference to a node, so consequently $iter(1) was individual references to each subnode, or row, of the parent table node. When using bundles, however, there are no parent nodes and subnodes. Instead, for $1 you return the number of bundle entries, and then $iter(1) will be the 1-based number iteration from 1 to getbundlenrentries(). Since $iter() is 1-based and bundles are 0-based, I subtract 1. So, recap: if $1 is a node reference, then $iter(1) will be a node reference as well, namely an iteration of references to the subnodes of the $1 parent node. If $1 is a number, then $iter(1) will also be a number, namely the iteration from 1 to $1. I know, it's complicated. But, at the same time, it's powerful and flexible.

We'll need to add that ability to put an expression (not just a straight column value) into the SELECT statement.

Hi Anthony,

Thanks a lot for the answer, this makes it clear. I now also understand the helpfile better

Hello,

Is there a way to use the result from one or more of the references in a new reference? Based on the example above i.e.

/*$5*/ use $4 in the calculation for this refreference:
/*$5*/ $4 * 100

I have tried to use something like
/*$5*/ $iter(4) * 100

but this does not seem to work.

Esther

Esther,
Yeah, you can't do that. You just have to copy/paste the expression from the $4 parameter in the spot where you want it to be in the $5 parameter.
Anthony

Hello Anthony,

That is what I am doing now, I hoped there was a more compact way, as it now results in very long calculations.

Esther