SQL Command for Left Join in Flexsim

Question

question

asb.p asked Jun 26, '19 Benjamin W2 commented Jul 10, '19

SQL Command for Left Join in Flexsim

I am facing challenges in using the Left join. Simple inner join is working fine

Table Result1 = Table.query("SELECT PersonSalary.Person, PersonSalary.Salary,PersonAllowance.Allowance FROM PersonSalary INNER JOIN PersonAllowance ON PersonSalary.Person = PersonAllowance.Person");
Table Final1 = Result1.cloneTo(Table("Result1"));

When I try to use left join. The flexsim is throwing exception error.

I want to have all the rows of PersonSalary table. If there is no allowance for that person the column allowance should show "No allowance".

Code used for the above requirement is

Table Result2 = Table.query("SELECT PersonSalary.Person, PersonSalary.Salary, CASE WHEN PersonAllowance.Allowance IS NULL THEN $1 ELSE PersonAllowance.Allowance END AS Allowance FROM PersonAllowance LEFT JOIN $1 ON PersonSalary.Person = PersonAllowance.Person","NoAllowance");
Table Final2 = Result2.cloneTo(Table("Result2"));

Expected output:

Person	Salary	Allowance
A	100	10
B	200	20
C	300	30
D	400	"No Allowance"
E	500	"No Allowance"

Please find the attached Flexsim model for the same.

sqlquerrymodel.fsm

Thanks in advance.

Software Version:

FlexSim 18.1.2

Software Version:

FlexSim 18.0.10

sql queries sql join

innerjoin.png (17.3 KiB)

sqlquerrymodel.fsm (16.9 KiB)

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Benjamin W2 commented · Jul 10, 2019 at 09:08 PM

Hey @asb.praneeth, was my answer helpful for you? If so, could you accept it? Otherwise, how else can I help?

0 ·

Answer 1 · 2019-06-26T15:53:33Z

Benjamin W2 answered Jun 26, '19

Hi @asb.praneeth,

Unfortunately, the FlexSim SQL parser does not support the LEFT JOIN function. Below is a link to a help page that details which SQL commands that FlexSim supports.

However, there is a code work-around:

Table Result2 = Table.query("SELECT * FROM PersonSalary");
Result2.cloneTo(Table("Result2"));

Table("Result2").addCol();
Table("Result2").setColHeader(3, "Allowance");

for (int i = 1; i <= Table("Result2").numRows; i++)
{
	string personSalaryPerson = Table("Result2")[i]["Person"];
	for (int j = 1; j <= Table("PersonAllowance").numRows; j++)
	{
		string personAllowancePerson = Table("PersonAllowance")[j]["Person"];
		if (personSalaryPerson == personAllowancePerson)
		{
			int personAllowance = Table("PersonAllowance")[j]["Allowance"];
			Table("Result2")[i][3] = personAllowance;
		}
	}
}

I hope this helps!

FlexSim SQL Reference:

https://docs.flexsim.com/en/19.1/Reference/DeveloperAdvancedUser/SQLQueries/

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 2 · 2019-06-26T15:59:12Z

Jordan Johnson answered Jun 26, '19 Benjamin W2 commented Jun 27, '19

The query() command (or Table.query()) only supports inner joins. When you get a sql compile exception, both the system console and compiler console open. On the compiler console, the first line reads:

syntax error, unexpected left, expecting end of code

Which means that the word "left" is not recognized by the parser.

To get the table you want, you can use the following FlexScript:

Table personSalary = Table("PersonSalary");
Table personAllowance = Table("PersonAllowance");
Table result = Table("Result2");
result.setSize(personSalary.numRows, 3);
result.setColHeader(1, "Person");
result.setColHeader(2, "Salary");
result.setColHeader(3, "Allowance");
for (int i = 1; i <= personSalary.numRows; i++) {
	Variant person = personSalary[i]["Person"];
	Variant salary = personSalary[i]["Salary"];
	Variant allowance = "No Allowance";
	for (int j = 1; j <= personAllowance.numRows; j++) {
		Variant matchPerson = personAllowance[j]["Person"];
		if (matchPerson == person) {
			allowance = personAllowance[j]["Allowance"];
			break;
		}
	}
	result[i][1] = person;
	result[i][2] = salary;
	result[i][3] = allowance;
}

· 3

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

asb.p commented · Jun 27, 2019 at 05:39 AM

I have to join tables with number of rows roughly

Table1 - 200000 rows and Table2 - 50000 rows

This will result in for loop run for 200000*50000 = 10000000000 iterations

Can you compare runtime for both cases? - let me know if it is comparatively the same?

0 ·

Steven Hamoen asb.p commented · Jun 27, 2019 at 07:05 AM

@asb.praneeth

Isn't it a lot easier if you test it yourself? You have the datasets. Just put the codes in the script window, adapt it to suit your table names and ready?

0 ·

Benjamin W2 asb.p commented · Jun 27, 2019 at 02:35 PM

Hi @asb.praneeth, I would agree with @steven.hamoen. Additionally, both Jordan and my solution are almost exactly the same, so I would imagine the run-time would be similar.

0 ·

question