[SQL] I cannot select a specific row from a statistics collector.

Question

question

Sebastien asked Jun 6, '18 Sebastien commented Jun 8, '18

[SQL] I cannot select a specific row from a statistics collector.

Hello everyone,

I have created a small model with 2 processors. I added a State Pie Chart and installed the template to be able to extract the total processing time for each processor.

Then I tried to do a calculated table to multiply the processing time by a value only for the processor 1. I wrote the following SQL request:

SELECT Processing FROM [Composite State Pie Collector] WHERE Object = 'Processor1'

Unfortunately just the title of the column appears. No data. I tried to use the same statistics collector and select the processing time where the idle was 0 for instance, and it worked. So i checked the type of the column object in the statistics collector and changed it from double to string. Yet it is not working. I tried using different quotation marks like 'Processor1' , "Processor1" and even ´Processor1´ but nothing works. I do not know where the problem is. Could anyone help me? Please find the model attached.

statecollector.fsm

Warm regards,

Software Version:

FlexSim 18.1.0

statistics collector sql

statecollector.fsm (24.9 KiB)

· 2

Arun Kr commented · Jun 06, 2018 at 05:01 PM

Hi @ Sébastien B2,

Create another column in the statistics collector called name and display the name of the object.

Then you use the query it will work. Your modified query will look like this.

SELECT Processing FROM [Composite State Pie Collector] WHERE Name = 'Processor1'

Regards,sebastian-support.fsm

Arun KR

2 ·

sebastian-support.fsm (27.9 KiB)

Sebastien Arun Kr commented · Jun 07, 2018 at 06:52 AM

Thank you for your answer. I chose to use the solution from Jordan as it fits more to my requirements.

Warm regards,

1 ·

Answer 1 · 2018-06-06T19:25:31Z

Jordan Johnson answered Jun 6, '18 Sebastien commented Jun 8, '18

In the model you have attached, the Statistics Collector is storing the ID of the object in the first column. However, the ID is a number, and in the model, the Statistics Collector is storing that number as text. The number should be stored as a double, and that value should be formatted as Object.

Once you make those changes, you can then use the following query in your Calculated Table:

SELECT Processing FROM [Composite State Pie Collector] 
WHERE Object = StatisticsCollector.getID(model().find("Processor1"))

statecollector-fixed.fsm

statecollector-fixed.fsm (28.8 KiB)

· 3

Sebastien commented · Jun 07, 2018 at 06:50 AM

Thank you for your answer ! It works great now.

I did not notice that the Object column changed to IDs. I did it again and first it contained a name (Processor1 and 2) although the column type is set to double. That is why I changed it to String afterwards and then the names were not displayed anymore but the IDs. I do not know why...

But then I have another problem how can I combine two queries into one ?

I would like to have somehting like this:

SELECT Processing*23/3600 AS Processor1 FROM [Composite State Pie Collector] WHERE Object = StatisticsCollector.getID(model().find("Processor1"))

AND

SELECT Processing*23/3600 AS Processor2 FROM [Composite State Pie Collector] WHERE Object = StatisticsCollector.getID(model().find("Processor2"))

in order to have a calculated table with 2 columns each coming from a different query. Is this possible ?

Warm regards,

0 ·

Jordan Johnson ♦♦ Sebastien commented · Jun 07, 2018 at 04:43 PM

It sounds like you are taking data that is in multiple rows of a single column, and trying to put it in a single row with multiple columns, which is basically a transposition. FlexSim's SQL doesn't include support for transposition.

If it really is a query on two objects, you can use this to do what you want:

SELECT 
	SUM(Processing1_internal) AS Processing1,
	SUM(Processing2_internal) AS Processing2
FROM (
	SELECT 
		CASE WHEN Object = StatisticsCollector.getID(model().find("Processor1"))
		THEN  Processing*23/3600
		ELSE null
		END
		AS Processing1_internal,
		CASE WHEN Object = StatisticsCollector.getID(model().find("Processor2"))
		THEN Processing*23/3600
		ELSE null
		END
		AS Processing2_internal
	FROM [Composite State Pie Collector] 
)<br>

Note that this does scale well. The more objects you want, the longer and more complicated the query gets.

statecollector-fixed-crazy-query.fsm

0 ·

statecollector-fixed-crazy-query.fsm (29.3 KiB)

Sebastien Jordan Johnson ♦♦ commented · Jun 08, 2018 at 07:53 AM

Thank you ! It works perfectly.

0 ·

question