question

Tommy Heyser avatar image
1 Like"
Tommy Heyser asked Jordan Johnson commented

SQL WITH Clause

I have a calculated table in which I'd like to filter out outliers greater than 3 standard deviations from the mean using SQL. The query is as follows: 

WITH data AS (
	SELECT
		cycle,
		(g_vi_e - g_vi_s)/60 AS cycletime
	FROM SC_18RdWSX
	GROUP BY 1
), data_with_stddev AS (
	SELECT
		cycle,
		cycletime,
		(cycletime - avg(cycletime) over ())
		/ (stddev(cycletime) over ()) AS zscore
	FROM data
	ORDER BY 1
)
SELECT * FROM data_with_stddev WHERE abs(zscore) < 3

I'm getting an error in the System Console as follows,

syntax error, unexpected identifier WITH, expecting end of code. Make sure commas, parentheses, etc. are placed correctly

Is the WITH clause permitted in FlexSim? If not, could anyone suggest a way to filter out outliers from Calculated Table results?

FlexSim 18.2.3
statistics collectorsql queries
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

Joerg Vogel avatar image
0 Likes"
Joerg Vogel answered Jordan Johnson commented

@Tommy Heyser, it is always a very good practice to look into the manual first before writing source code. You are using a FlexSim Version with a most limited amount of SQL clauses. Please look into the program manual of your version yourself: Reference > Developer and Advanced User Reference SQL Queries > [scroll to the end. There you find all allowed clauses in your current version.] The “WITH” clause is still not available in FlexSim 2020.

It looks like you compose your data by querying and computing from a data source by several sub queries. Then why don’t you try to do this by putting the result of your sub queries into calculated tables for each single sub query. You can join these tables to get your final query.

· 2
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Tommy Heyser avatar image Tommy Heyser commented ·

Hi @Jörg Vogel,

Thanks for the reply. I had looked at the SQL Queries section in the user manual as you had mentioned. I saw that the supported SQL parser commands are only a subset of standard SQL and WITH isn't included in the list of supported commands for 18.2.3 (the version I'm using / updating an old model so can't use more recent version). I figured I'd asked to confirm just in case.

Thanks for the suggestion about putting the results into calculated tables. I hadn't thought about using multiple calculated tables, was trying to do everything in a single table. This simplifies things quite a bit with the SQL queries.

Cheers, Tommy H

0 Likes 0 ·
Jordan Johnson avatar image Jordan Johnson ♦♦ Tommy Heyser commented ·

You can also use nested queries, which may be simpler than using multiple tables.

2 Likes 2 ·