# Adding a column for Description based on tag value

**URL:** <https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753>\
**Category:** Ignition\
**Tags:** ignition81, namedqueries, database, vision\
**Created:** [October 27, 2022, 12:46pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753 "2022-10-27T12:46:23Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 27, 2022, 12:46pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/1 "2022-10-27T12:46:23Z")

</div>

I have been able to create a table with the help of the forum. It is able to count frequency and duration. I have two databases.

1. logs the machine state code with a t\_stamp
2. Machine state with description

I am trying to get the table to replace or add column (not sure which would be more straightforward because I can hide the column) based on machine state code from the first table = description on the second table

I am able to write the basic INNER JOIN among the two tables, but I am struggling to find a way to join them in the existing query. I've tried adding the JOIN function between the subq and after, but I have not found luck.

I have the working INNER JOIN function in comments now for reference. Please do not hold back if there is a better way to do this that I am unaware of. Thank you in advance!!

Using PostgreSQL v15

```auto
SELECT "Machine_State_Code",

COUNT ("Machine_State_Code") AS "FREQUENCY",

SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"

FROM (
	SELECT 
	"t_stamp",
	"Machine_State_Code",
		coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
			CASE WHEN current_timestamp < :end_ts 
				THEN current_timestamp 
				ELSE :end_ts 
				END) AS t_stamp1
		FROM {machine_machinestatecode}
		WHERE "t_stamp" >= coalesce(( 
			SELECT "t_stamp" 
				FROM {machine_machinestatecode}
				WHERE "t_stamp" < :begin_ts 
				ORDER BY "t_stamp" DESC LIMIT 1), 'Tue Oct 26 18:00:00 EDT 2021')
				
		AND "t_stamp" <= coalesce(( 
			SELECT "t_stamp" 
			FROM {machine_machinestatecode}
			WHERE "t_stamp" > :end_ts  
			ORDER BY "t_stamp" DESC LIMIT 1), 'Fri Oct 26 18:00:00 EDT 2029')
			
	) subq

WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	

GROUP BY "Machine_State_Code"
ORDER BY count("Machine_State_Code") DESC

/*
SELECT *
FROM pou102_machinestatecode
  INNER JOIN pou102_descriptions ON pou102_machinestatecode."Machine_State_Code" =
    pou102_descriptions."State"
*/

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/0/20fff119b7ebd5e715ae3aa5c363a63c4ce75c86.png)

```auto
SELECT *
FROM pou102_machinestatecode
  INNER JOIN pou102_descriptions ON pou102_machinestatecode."Machine_State_Code" =
    pou102_descriptions."State"

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/9/09afca9477518df01a889eef8b58ccd91b696ffc.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 27, 2022, 12:49pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/2 "2022-10-27T12:49:40Z")

</div>

It is most efficient to perform that JOIN on as few rows as practical. At the last possible moment, so to speak. Wrap your query into another subquery, then join the final result to the descriptions. Consider using a LEFT JOIN, so that you won't skip codes that are missing a description.

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 27, 2022, 1:48pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/3 "2022-10-27T13:48:50Z")

</div>

Thank you for the suggestion!! That makes sense. I am receiving an error regarding subq."Machine\_State\_Code"  
I've added that to the last GROUP BY, but it is not recognizing it. I don't believe I need to subq."Machine\_State\_Code" within the subq. Am I mistaken?

```auto
ERROR: column "subq.Machine_State_Code" must appear in the GROUP BY clause or be used in an aggregate function Position: 47

```

```auto
SELECT "Description", "State"
	
FROM(

SELECT subq."Machine_State_Code",

COUNT (subq."Machine_State_Code") AS "FREQUENCY",

SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"

FROM (
	SELECT 
	"t_stamp",
	"Machine_State_Code",
		coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
			CASE WHEN current_timestamp < :end_ts 
				THEN current_timestamp 
				ELSE :end_ts 
				END) AS t_stamp1
		FROM {machine_machinestatecode}
		WHERE "t_stamp" >= coalesce(( 
			SELECT "t_stamp" 
				FROM {machine_machinestatecode}
				WHERE "t_stamp" < :begin_ts 
				ORDER BY "t_stamp" DESC LIMIT 1), 'Tue Oct 26 18:00:00 EDT 2021')
				
		AND "t_stamp" <= coalesce(( 
			SELECT "t_stamp" 
			FROM {machine_machinestatecode}
			WHERE "t_stamp" > :end_ts  
			ORDER BY "t_stamp" DESC LIMIT 1), 'Fri Oct 26 18:00:00 EDT 2029')
			
	) subq
	WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	

    ) subq1
	LEFT JOIN pou102_descriptions ON subq."Machine_State_Code" =
    pou102_descriptions."State"
    
GROUP BY subq."Machine_State_Code"
ORDER BY count(subq."Machine_State_Code") DESC

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 27, 2022, 2:26pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/4 "2022-10-27T14:26:56Z")

</div>

Use the subquery alias appropriate to the level of nesting. `subq1."Machine_State_Code"`.

{Try to indent for each subq level.. it's had to track your syntax.}

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 27, 2022, 3:13pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/5 "2022-10-27T15:13:33Z")

</div>

Sorry about that! I am super new to this style of programming. I am receiving an error about missing a FROM-clause entry for table "subq1"

```auto
SELECT "Description", "State"
	
	FROM(

		SELECT "Machine_State_Code",
		COUNT ("Machine_State_Code") AS "FREQUENCY",
		SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"
		 
			FROM (
				SELECT "t_stamp", subq1."Machine_State_Code", coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
					CASE WHEN current_timestamp < :end_ts 
					THEN current_timestamp 
					ELSE :end_ts 
					END) AS t_stamp1
						FROM {machine_machinestatecode}
							WHERE "t_stamp" >= coalesce(( 
							SELECT "t_stamp" 
								FROM {machine_machinestatecode}
									WHERE "t_stamp" < :begin_ts 
									ORDER BY "t_stamp" DESC LIMIT 1), 'Tue Oct 26 18:00:00 EDT 2021')
									AND "t_stamp" <= coalesce(( 
									SELECT "t_stamp" 
										FROM {machine_machinestatecode}
											WHERE "t_stamp" > :end_ts  
											ORDER BY "t_stamp" DESC LIMIT 1), 'Fri Oct 26 18:00:00 EDT 2029')
											
				) subq
			
	WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	

    ) subq1

LEFT JOIN pou102_descriptions ON subq1."Machine_State_Code" = pou102_descriptions."State"
	 
GROUP BY subq1."Machine_State_Code"
ORDER BY count(subq1."Machine_State_Code") DESC

```

I've tried adding the FROM pou102\_descriptions above and below the  
`	WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	`.  
but only receive syntax errors

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 27, 2022, 4:54pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/6 "2022-10-27T16:54:39Z")

</div>

Working code. Thank you for the help @pturmel !!

```auto
SELECT 
	*
FROM (
	SELECT 
		subq."Machine_State_Code",
		COUNT (subq."Machine_State_Code") AS "FREQUENCY",
		SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"
	FROM (
		SELECT 
			"t_stamp",
			"Machine_State_Code",
			coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"), CASE WHEN current_timestamp < :end_ts THEN current_timestamp ELSE :end_ts END) AS t_stamp1
		FROM 
			{machine_machinestatecode}
		WHERE 
			"t_stamp" >= coalesce(( SELECT "t_stamp" FROM {machine_machinestatecode} WHERE "t_stamp" < :begin_ts ORDER BY "t_stamp" DESC LIMIT 1), 'Tue Oct 26 18:00:00 EDT 2021')				
				AND "t_stamp" <= coalesce((SELECT "t_stamp" FROM {machine_machinestatecode} WHERE "t_stamp" > :end_ts ORDER BY "t_stamp" DESC LIMIT 1), 'Fri Oct 26 18:00:00 EDT 2029')
		) subq
		
		WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts
		
		
	GROUP BY subq."Machine_State_Code"
	ORDER BY count(subq."Machine_State_Code") DESC 
	
	) subq1
	LEFT JOIN pou102_descriptions ON subq1."Machine_State_Code" = pou102_descriptions."State"

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 27, 2022, 4:57pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/7 "2022-10-27T16:57:07Z")

</div>

I noticed you are mixing value parameters (colon prefix) with querystring parameters (curly braces). You should change your named query to use only value parameters unless you have a very specific need for querystring parameters. And then go to the effort to audit all of the paths that supply data to querystrings that could be hacked.

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 27, 2022, 6:00pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/8 "2022-10-27T18:00:20Z")

</div>

Thank you for the suggestion. I will look into this. Last question (hopefully). Is there a straightforward way to reorganize the columns so that Description is first?  
How it comes out after executing query:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/1/011633b42d8469e565797bcfcd9709699e5ebbaf.png)  
How I would like it to come out:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/f/bfc7577d300e7755e3befeee1915836ddc04110f.png)  
Then I could just hide the additional columns in the table on the vision page.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 27, 2022, 6:02pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/9 "2022-10-27T18:02:07Z")

</div>

The order of columns in the outermost SELECT list should be the order delivered.

---

<div class="post-metadata">

**Author:** ![Zach\_Stewart](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@Zach\_Stewart](https://forum.inductiveautomation.com/u/Zach_Stewart)\
**Post date:** [October 27, 2022, 6:03pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/10 "2022-10-27T18:03:20Z")

</div>

Wow, I feel silly. A HUGE THANK YOU @pturmel

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 27, 2022, 6:11pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/11 "2022-10-27T18:11:42Z")

</div>

Perhaps this is a more readable format of your query - not that I've completely figured out what you're doing.

```auto
SELECT "Description", "State"
FROM(
	SELECT "Machine_State_Code",
	COUNT ("Machine_State_Code") AS "FREQUENCY",
	SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"
	FROM (
		SELECT "t_stamp", subq1."Machine_State_Code", 
			COALESCE(
				LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
				CASE WHEN current_timestamp < :end_ts 
				THEN current_timestamp 
				ELSE :end_ts 
				END
			) AS t_stamp1
		FROM {machine_machinestatecode}
		WHERE "t_stamp" >= COALESCE(
			( 
				SELECT "t_stamp" 
				FROM {machine_machinestatecode}
				WHERE "t_stamp" < :begin_ts 
				ORDER BY "t_stamp" DESC LIMIT 1
			)
			, 'Tue Oct 26 18:00:00 EDT 2021'
		)
		AND "t_stamp" <= COALESCE(
			( 
				SELECT "t_stamp" 
				FROM {machine_machinestatecode}
				WHERE "t_stamp" > :end_ts  
				ORDER BY "t_stamp" DESC 
				LIMIT 1
			), 
			'Fri Oct 26 18:00:00 EDT 2029'
		)
	) subq
	WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	
) subq1

LEFT JOIN pou102_descriptions ON subq1."Machine_State_Code" = pou102_descriptions."State"
	 
GROUP BY subq1."Machine_State_Code"
ORDER BY COUNT(subq1."Machine_State_Code") DESC

```

It might make it easier for the next guy.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [October 28, 2022, 1:39pm UTC](https://forum.inductiveautomation.com/t/adding-a-column-for-description-based-on-tag-value/65753/12 "2022-10-28T13:39:09Z")

</div>

I can only imagine that those coalesces in your where clauses will be terrible for query performance, especially when you scale up the table size. You will be better off pulling that out and passing it in so you're just using a datetime in the where and no functions.

Edit: then again, maybe they're not so bad... 🤔