Named Query across multiple partitions

Christopher_Drum · March 21, 2024, 4:27pm

Hi, working on a customers system that originally did not have partitioning setup for the Historical Tag Provider. Over a year ago partitioning was enabled and set to monthly partitioning. Ever since that change was made, the named queries stopped working for any date range set after the day partitioning was enabled.

Is there a way to change the named query below to look across multiple tables?

SELECT
	-- The record's timestamp is the earliest timestamp of all the tests available
	LEAST(cfe_s, clearwell_s, low_cl_s, fb1_s, fb2_s, fb3_s, fb4_s, fb5_s, fb6_s, fb7_s, fb8_s) date_time, 
	-- The test results
	cfe_s, cfe_sv, cfe_sp, cfe_pass, 
	clearwell_s, clearwell_sv, clearwell_sp, clearwell_pass,
	low_cl_s, low_cl_sv, low_cl_sp, low_cl_pass,
	fb1_s, fb1_sv, fb1_sp, fb1_pass,
	fb2_s, fb2_sv, fb2_sp, fb2_pass,
	fb3_s, fb3_sv, fb3_sp, fb3_pass,
	fb4_s, fb4_sv, fb4_sp, fb4_pass,
	fb5_s, fb5_sv, fb5_sp, fb5_pass,
	fb6_s, fb6_sv, fb6_sp, fb6_pass,
	fb7_s, fb7_sv, fb7_sp, fb7_pass,
	fb8_s, fb8_sv, fb8_sp, fb8_pass
FROM (
	-- CFE 
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) cfe_s, 
				(SELECT floatvalue FROM get_tag_data('filter plant/cfe/turbidity', NULL, _d.t_stamp, false, 1)) cfe_sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/cfe/set points/turbidity high/alarm', NULL, _d.t_stamp, false, 1)) cfe_sp
			FROM get_tag_data('filter plant/cfe/alarms/high', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/cfe/alarms/test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT
			cfe_s, cfe_sv, cfe_sp, (cfe_sv >= cfe_sp) cfe_pass,
			-- Provide the column names for the other tests here
			NULL::TIMESTAMP WITH TIME ZONE clearwell_s, NULL::DOUBLE PRECISION clearwell_sv, NULL::DOUBLE PRECISION clearwell_sp, NULL::BOOLEAN clearwell_pass,
			NULL::TIMESTAMP WITH TIME ZONE low_cl_s, NULL::DOUBLE PRECISION low_cl_sv, NULL::DOUBLE PRECISION low_cl_sp, NULL::BOOLEAN low_cl_pass,
			NULL::TIMESTAMP WITH TIME ZONE fb1_s, NULL::DOUBLE PRECISION fb1_sv, NULL::DOUBLE PRECISION fb1_sp, NULL::BOOLEAN fb1_pass,
			NULL::TIMESTAMP WITH TIME ZONE fb2_s, NULL::DOUBLE PRECISION fb2_sv, NULL::DOUBLE PRECISION fb2_sp, NULL::BOOLEAN fb2_pass,
			NULL::TIMESTAMP WITH TIME ZONE fb3_s, NULL::DOUBLE PRECISION fb3_sv, NULL::DOUBLE PRECISION fb3_sp, NULL::BOOLEAN fb3_pass,
			NULL::TIMESTAMP WITH TIME ZONE fb4_s, NULL::DOUBLE PRECISION fb4_sv, NULL::DOUBLE PRECISION fb4_sp, NULL::BOOLEAN fb4_pass,
			NULL::TIMESTAMP WITH TIME ZONE fb5_s, NULL::DOUBLE PRECISION fb5_sv, NULL::DOUBLE PRECISION fb5_sp, NULL::BOOLEAN fb5_pass,
			NULL::TIMESTAMP WITH TIME ZONE fb6_s, NULL::DOUBLE PRECISION fb6_sv, NULL::DOUBLE PRECISION fb6_sp, NULL::BOOLEAN fb6_pass,
			NULL::TIMESTAMP WITH TIME ZONE fb7_s, NULL::DOUBLE PRECISION fb7_sv, NULL::DOUBLE PRECISION fb7_sp, NULL::BOOLEAN fb7_pass,
			NULL::TIMESTAMP WITH TIME ZONE fb8_s, NULL::DOUBLE PRECISION fb8_sv, NULL::DOUBLE PRECISION fb8_sp, NULL::BOOLEAN fb8_pass
		FROM vals)
	UNION ALL
	
	-- Clearwell Low Level
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/clearwell/level', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/clearwell/set points/level/low/alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/clearwell/alarms/level/low', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/clearwell/alarms/level/low test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv <= sp),
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL 
		FROM vals)
	UNION ALL
	
	-- Clearwell Low Chlorine
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/clearwell/chlorine/value', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/clearwell/set points/chlorine/low/alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/clearwell/alarms/chlorine/low', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/clearwell/alarms/chlorine/low test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv <= sp),
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL
		FROM vals)
	UNION ALL
	
	-- Filter Bed 1
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 1/turbidity', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 1/set points/high alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/filter bed 1/alarms/high', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/filter bed 1/alarms/test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv >= sp),
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL
		FROM vals)
	UNION ALL
	
	-- Filter Bed 2
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 2/turbidity', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 2/set points/high alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/filter bed 2/alarms/high', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/filter bed 2/alarms/test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv >= sp),
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL
		FROM vals)
	UNION ALL
	
	-- Filter Bed 3
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 3/turbidity', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 3/set points/high alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/filter bed 3/alarms/high', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/filter bed 3/alarms/test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv >= sp),
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL
		FROM vals)
	UNION ALL
	
	-- Filter Bed 4
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 4/turbidity', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 4/set points/high alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/filter bed 4/alarms/high', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/filter bed 4/alarms/test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv >= sp),
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL
		FROM vals)
	UNION ALL
	
	-- Filter Bed 5
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 5/turbidity', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 5/set points/high alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/filter bed 5/alarms/high', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/filter bed 5/alarms/test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv >= sp),
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL
		FROM vals)
	UNION ALL
	
	-- Filter Bed 6
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 6/turbidity', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 6/set points/high alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/filter bed 6/alarms/high', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/filter bed 6/alarms/test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv >= sp),
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL
		FROM vals)
	UNION ALL
	
	-- Filter Bed 7
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 7/turbidity', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 7/set points/high alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/filter bed 7/alarms/high', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/filter bed 7/alarms/test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv >= sp),
			NULL, NULL, NULL, NULL
		FROM vals)
	UNION ALL
	
	-- Filter Bed 8
	(WITH vals AS (
			SELECT 
				to_timestamp(t_stamp / 1000) date_time, 
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 8/turbidity', NULL, _d.t_stamp, false, 1)) sv,
				(SELECT floatvalue FROM get_tag_data('filter plant/filter bed 8/set points/high alarm', NULL, _d.t_stamp, false, 1)) sp
			FROM get_tag_data('filter plant/filter bed 8/alarms/high', :start::TIMESTAMP WITH TIME ZONE, :end::TIMESTAMP WITH TIME ZONE) _d
			WHERE intvalue=1 AND (SELECT intvalue FROM get_tag_data('filter plant/filter bed 8/alarms/test mode', NULL, _d.t_stamp, false, 1))=1)
		SELECT 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL,
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL, 
			NULL, NULL, NULL, NULL,
			date_time, sv, sp, (sv >= sp)
		FROM vals)
	) _t
ORDER BY date_time

Thank You!

pturmel · March 21, 2024, 4:36pm

You would have to use UNION ALL with all of the relevant partition tables.

Better would be to not use named queries (or any others) with historian tables. Use the historian's native query functions. They exist to hide these implementation details.

(Also, as noted in your other topic, don't use the historian for tasks for which it isn't properly capable, like capturing multiple values at common timestamps.)

Christopher_Drum · March 21, 2024, 4:51pm

Thank You! I will definitely look towards using the transaction group for the original purpose of this named query and the other topic I posted. This named query was originally written by someone else for the purpose of doing what you do not recommend. There were other named queries that were used for reports, I have since modified those reports to use ignitions native tag historian query functions, this was the only one I did not modify as it was the most complex and I did not understand what he was trying to do. I have zero knowledge of queries and databases.