Add text collum into select in sql report

Ignition
1

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

2

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?

3

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.

1 Like
4

Glad it was an easy fix!

5

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

3 Likes
6

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