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: 

  1. WITH data AS (
  2. 	SELECT
  3. 		cycle,
  4. 		(g_vi_e - g_vi_s)/60 AS cycletime
  5. 	FROM SC_18RdWSX
  6. 	GROUP BY 1
  7. ), data_with_stddev AS (
  8. 	SELECT
  9. 		cycle,
  10. 		cycletime,
  11. 		(cycletime - avg(cycletime) over ())
  12. 		/ (stddev(cycletime) over ()) AS zscore
  13. 	FROM data
  14. 	ORDER BY 1
  15. )
  16. SELECT * FROM data_with_stddev WHERE abs(zscore) < 3

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

  1. 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

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 Best Answer

@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

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