ATTENTION

This FlexSim Community Forum is read-only. Please post any new questions, ideas, or discussions to our new community (we call it Answers) at https://answers.flexsim.com/. Our new Question & Answer site brings a modern, mobile-friendly interface and more focus on getting answers quickly. There are a few differences between how our new Q&A community works vs. a classic, threaded-conversation-style forum like the one below, so be sure to read our Answers Best Practices.


flexsim.com

Go Back   FlexSim Community Forum > FlexSim Software > Q&A
Downloads

Q&A Using Flexsim and building models

  #1  
Old 05-09-2014
Esther Bennett Esther Bennett is offline
Flexsim User
 
Join Date: Mar 2008
Posts: 115
Downloads: 27
Uploads: 0
Thanks: 103
Thanked 116 Times in 50 Posts
Rep Power: 271
Esther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to all
Default 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
  #2  
Old 05-09-2014
Anthony Johnson's Avatar
Anthony Johnson Anthony Johnson is offline
Manager of Product Development
 
Join Date: Jul 2007
Posts: 440
Downloads: 86
Uploads: 4
Thanks: 171
Thanked 899 Times in 288 Posts
Rep Power: 735
Anthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond repute
Default

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:
Code:
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:
Code:
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,
Code:
Name,     ID
BoxA,      1
SphereA,   2
BoxB,      3
This will return the BoxA and BoxB rows.
The Following 5 Users Say Thank You to Anthony Johnson For This Useful Post:
Tom David (05-12-2014)
  #3  
Old 05-10-2014
Esther Bennett Esther Bennett is offline
Flexsim User
 
Join Date: Mar 2008
Posts: 115
Downloads: 27
Uploads: 0
Thanks: 103
Thanked 116 Times in 50 Posts
Rep Power: 271
Esther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to all
Default

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
  #4  
Old 05-19-2014
Esther Bennett Esther Bennett is offline
Flexsim User
 
Join Date: Mar 2008
Posts: 115
Downloads: 27
Uploads: 0
Thanks: 103
Thanked 116 Times in 50 Posts
Rep Power: 271
Esther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to all
Default

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
  #5  
Old 05-20-2014
Anthony Johnson's Avatar
Anthony Johnson Anthony Johnson is offline
Manager of Product Development
 
Join Date: Jul 2007
Posts: 440
Downloads: 86
Uploads: 4
Thanks: 171
Thanked 899 Times in 288 Posts
Rep Power: 735
Anthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond repute
Default

To do the same query as above except on bundles, change it to the following:
Code:
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.

Last edited by Anthony Johnson; 05-20-2014 at 10:15 AM.
The Following User Says Thank You to Anthony Johnson For This Useful Post:
Esther Bennett (05-20-2014)
  #6  
Old 05-20-2014
Esther Bennett Esther Bennett is offline
Flexsim User
 
Join Date: Mar 2008
Posts: 115
Downloads: 27
Uploads: 0
Thanks: 103
Thanked 116 Times in 50 Posts
Rep Power: 271
Esther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to all
Default

Hi Anthony,

Thanks a lot for the answer, this makes it clear. I now also understand the helpfile better
  #7  
Old 06-23-2014
Esther Bennett Esther Bennett is offline
Flexsim User
 
Join Date: Mar 2008
Posts: 115
Downloads: 27
Uploads: 0
Thanks: 103
Thanked 116 Times in 50 Posts
Rep Power: 271
Esther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to all
Default

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
  #8  
Old 06-26-2014
Anthony Johnson's Avatar
Anthony Johnson Anthony Johnson is offline
Manager of Product Development
 
Join Date: Jul 2007
Posts: 440
Downloads: 86
Uploads: 4
Thanks: 171
Thanked 899 Times in 288 Posts
Rep Power: 735
Anthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond repute
Default

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
  #9  
Old 06-27-2014
Esther Bennett Esther Bennett is offline
Flexsim User
 
Join Date: Mar 2008
Posts: 115
Downloads: 27
Uploads: 0
Thanks: 103
Thanked 116 Times in 50 Posts
Rep Power: 271
Esther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to allEsther Bennett is a name known to all
Default

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

Tags
sql


Thread Thread Starter Forum Replies Last Post
Lambda functions in FlexScript Allister Wilson User Development 2 07-25-2013 05:42 AM
Questions about new drawsurrogate functions Steven Hamoen Q&A 3 07-05-2010 11:34 AM
How to create probability functions in patient tracks? Lionel Lim FlexSim HC: Q&A 9 11-20-2009 03:29 PM
While functions Xavier Jackson Q&A 12 08-12-2008 01:32 PM
BasicFR Advanced Functions Cliff King Q&A 0 11-13-2007 08:02 PM


All times are GMT -6.
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Copyright 1993-2018 FlexSim Software Products, Inc.