# Add text collum into select in sql report

**URL:** <https://forum.inductiveautomation.com/t/add-text-collum-into-select-in-sql-report/42237>\
**Category:** Ignition\
**Tags:** ignition80\
**Created:** [January 14, 2021, 10:46am UTC](https://forum.inductiveautomation.com/t/add-text-collum-into-select-in-sql-report/42237 "2021-01-14T10:46:30Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![jakub.moric](https://avatars.discourse-cdn.com/v4/letter/j/b38774/32.png) [@jakub.moric](https://forum.inductiveautomation.com/u/jakub.moric)\
**Post date:** [January 14, 2021, 10:46am UTC](https://forum.inductiveautomation.com/t/add-text-collum-into-select-in-sql-report/42237/1 "2021-01-14T10:46:30Z")

</div>

Dear All,  
I would like to ask about reports,

I have a report and as datasource i have sql query

SELECT

```
		  Production_Analysis2.Date,
		  Production_Analysis2.Shift,
		  Production_Analysis2.StatTotStpTime,
		  Production_Analysis2.StatAvrCycleTime,
		  Production_Analysis2.StatTotLostInj,
		  Production_Analysis2.StatNShoes,
		  Production_Analysis2.AutoMissLastHandRob,
		  Production_Analysis2.AutoSprayRobot,
		  Production_Analysis2.AutoColourChange1,
		  Production_Analysis2.AutoScrewChange,
		  Production_Analysis2.AutoOthers,
		  Production_Analysis2.StatMedCycleTime,
		  Production_Analysis2.StatActCycleTime
		FROM Production_Analysis2
		WHERE Len(Production_Analysis2.Date) = 10 AND
			 CONVERT(datetime,Production_Analysis2.Date,104)BETWEEN dateadd(day,-1,format(CURRENT_TIMESTAMP,'yyyy-MM-dd')) AND dateadd(day,-1,format(CURRENT_TIMESTAMP,'yyyy-MM-dd'))

```

union all  
Select  
Production\_Analysis3.Date,  
Production\_Analysis3.Shift,  
Production\_Analysis3.StatTotStpTime,  
Production\_Analysis3.StatAvrCycleTime,  
Production\_Analysis3.StatTotLostInj,  
Production\_Analysis3.StatNShoes,  
Production\_Analysis3.AutoMissLastHandRob,  
Production\_Analysis3.AutoSprayRobot,  
Production\_Analysis3.AutoColourChange1,  
Production\_Analysis3.AutoScrewChange,  
Production\_Analysis3.AutoOthers,  
Production\_Analysis3.StatMedCycleTime,  
Production\_Analysis3.StatActCycleTime  
FROM Production\_Analysis3  
WHERE Len(Production\_Analysis3.Date) = 10 AND  
CONVERT(datetime,Production\_Analysis3.Date,104) BETWEEN dateadd(day,-1,format(CURRENT\_TIMESTAMP,‘yyyy-MM-dd’)) AND dateadd(day,-1,format(CURRENT\_TIMESTAMP,‘yyyy-MM-dd’))

and what i want to do is add new collum which is not in database and put there value, In sql it is working easilly like,

SELECT  
‘Machine1’ as Machine,  
Production\_Analysis2.Date,  
Production\_Analysis2.Shift,  
Production\_Analysis2.StatTotStpTime,  
Production\_Analysis2.StatAvrCycleTime,  
Production\_Analysis2.StatTotLostInj,  
Production\_Analysis2.StatNShoes,  
Production\_Analysis2.AutoMissLastHandRob,  
Production\_Analysis2.AutoSprayRobot,  
Production\_Analysis2.AutoColourChange1,  
Production\_Analysis2.AutoScrewChange,  
Production\_Analysis2.AutoOthers,  
Production\_Analysis2.StatMedCycleTime,  
Production\_Analysis2.StatActCycleTime  
FROM Production\_Analysis2  
WHERE Len(Production\_Analysis2.Date) = 10 AND  
CONVERT(datetime,Production\_Analysis2.Date,104)BETWEEN dateadd(day,-1,format(CURRENT\_TIMESTAMP,‘yyyy-MM-dd’)) AND dateadd(day,-1,format(CURRENT\_TIMESTAMP,‘yyyy-MM-dd’))  
union all  
Select  
‘Machine2’ as Machine,  
Production\_Analysis3.Date,  
Production\_Analysis3.Shift,  
Production\_Analysis3.StatTotStpTime,  
Production\_Analysis3.StatAvrCycleTime,  
Production\_Analysis3.StatTotLostInj,  
Production\_Analysis3.StatNShoes,  
Production\_Analysis3.AutoMissLastHandRob,  
Production\_Analysis3.AutoSprayRobot,  
Production\_Analysis3.AutoColourChange1,  
Production\_Analysis3.AutoScrewChange,  
Production\_Analysis3.AutoOthers,  
Production\_Analysis3.StatMedCycleTime,  
Production\_Analysis3.StatActCycleTime  
FROM Production\_Analysis3  
WHERE Len(Production\_Analysis3.Date) = 10 AND  
CONVERT(datetime,Production\_Analysis3.Date,104) BETWEEN dateadd(day,-1,format(CURRENT\_TIMESTAMP,‘yyyy-MM-dd’)) AND dateadd(day,-1,format(CURRENT\_TIMESTAMP,‘yyyy-MM-dd’))

but i cant get it working in the reports in ignition designer.

Thank you  
Best Regards Jakub

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [January 14, 2021, 2:58pm UTC](https://forum.inductiveautomation.com/t/add-text-collum-into-select-in-sql-report/42237/2 "2021-01-14T14:58:54Z")

</div>

> [@jakub.moric](#):
>
> i cant get it working in the reports in ignition designer.

Does this mean you get an error message and no data is retrieved? If so, please post that error message.

If there's no error, what part isn't working? Is the XML in the preview tab correct?

---

<div class="post-metadata">

**Author:** ![jakub.moric](https://avatars.discourse-cdn.com/v4/letter/j/b38774/32.png) [@jakub.moric](https://forum.inductiveautomation.com/u/jakub.moric)\
**Post date:** [January 15, 2021, 6:12am UTC](https://forum.inductiveautomation.com/t/add-text-collum-into-select-in-sql-report/42237/3 "2021-01-15T06:12:08Z")

</div>

Oh now when i see error message i know where i made a mistake

– All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I forgot to put ‘MachineX’ as Machine in to all selects.

Sorry this topic can be deleted.

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [January 15, 2021, 3:00pm UTC](https://forum.inductiveautomation.com/t/add-text-collum-into-select-in-sql-report/42237/4 "2021-01-15T15:00:26Z")

</div>

Glad it was an easy fix!

---

<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:** [January 15, 2021, 3:17pm UTC](https://forum.inductiveautomation.com/t/add-text-collum-into-select-in-sql-report/42237/5 "2021-01-15T15:17:18Z")

</div>

> [@jakub.moric](#):
>
> Sorry this topic can be deleted.

Please don't. The fixes to even simple mistakes can be helpful to others.

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [January 15, 2021, 6:40pm UTC](https://forum.inductiveautomation.com/t/add-text-collum-into-select-in-sql-report/42237/6 "2021-01-15T18:40:00Z")

</div>

I agree. The first thing I do if I’m having a problem with anything is search the forums.