question

Sebastien avatar image
0 Likes"
Sebastien asked Sebastien commented

[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,

FlexSim 18.1.0
statistics collectorsql
statecollector.fsm (24.9 KiB)
· 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.

Arun Kr avatar image Arun Kr commented ·

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 Likes 2 ·
sebastian-support.fsm (27.9 KiB)
Sebastien avatar image Sebastien Arun Kr commented ·

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

Warm regards,

1 Like 1 ·

1 Answer

Jordan Johnson avatar image
1 Like"
Jordan Johnson answered Sebastien commented

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

Sebastien avatar image Sebastien commented ·

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 Likes 0 ·
Jordan Johnson avatar image Jordan Johnson ♦♦ Sebastien commented ·

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 Likes 0 ·
statecollector-fixed-crazy-query.fsm (29.3 KiB)
Sebastien avatar image Sebastien Jordan Johnson ♦♦ commented ·

Thank you ! It works perfectly.

0 Likes 0 ·