# Force Select Query in Report

**URL:** <https://forum.inductiveautomation.com/t/force-select-query-in-report/70490>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [March 1, 2023, 5:34pm UTC](https://forum.inductiveautomation.com/t/force-select-query-in-report/70490 "2023-03-01T17:34:29Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://forum.inductiveautomation.com/u/dturner)\
**Post date:** [March 1, 2023, 5:34pm UTC](https://forum.inductiveautomation.com/t/force-select-query-in-report/70490/1 "2023-03-01T17:34:29Z")

</div>

I am trying to put the results of a SQL query into a table in a report but I am getting an error. The system thinks the the query is an update query. Is there a way to force it to think it is a Select query?

The code works in SQL Server Management Studio.

I have tried creating a Stored Procedure and get the same error.

SQL Query

```auto
declare @Previous_2_Days date
declare @Start_Time varchar(Max) 
declare @End_Time varchar(Max)
declare @Previous_day_date date
declare @Third_Shift_Start varchar(Max)
declare @Third_Shift_End varchar(Max)

Set @Start_Time = '18:30:00.000'
Set @End_Time = '05:29:59.999'
Set @Previous_2_Days = (SELECT Convert(date, dateadd(day,datediff(day,2,GETDATE()),0)));
Set @Previous_day_date = (SELECT Convert(date, dateadd(day,datediff(day,1,GETDATE()),0)));
Set @Third_Shift_Start = '' + convert(varchar, @Previous_2_Days) + ' ' + @Start_Time + ''
Set @Third_Shift_End = '' + convert(varchar, @Previous_day_date) + ' ' + @End_Time + ''

If Exists
	(
		Select QR_Code as 'QR Code', Count(QR_Code) as 'Number of Duplicates'
		From Laser_Marker_Success_Rate
		Where DT_stamp Between @Third_Shift_Start and @Third_Shift_End 
		Group by QR_Code
		Having Count(QR_Code) > 1
	)
Begin 
		Select QR_Code as 'QR Code', Count(QR_Code) as 'Number of Duplicates'
		From Laser_Marker_Success_Rate
		Where DT_stamp Between @Third_Shift_Start and @Third_Shift_End 
		Group by QR_Code
		Having Count(QR_Code) > 1
End

Else	
	
	Select 'No Duplicates Codes' as 'QR Code', 'Number of Duplicates' = 0

```

System Info.  
Ignition version - Version: 7.9.5  
OS - Windows Server 2012 R2

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [March 1, 2023, 5:40pm UTC](https://forum.inductiveautomation.com/t/force-select-query-in-report/70490/2 "2023-03-01T17:40:44Z")

</div>

I _think_ 7.9.5 has named queries - you could put your query into a named query, where you can force the query mode to select.

You should really update to the latest 7.9 version sooner, rather than later, by the way. 7.9.5 is quite old and has some serious bugs.

---

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://forum.inductiveautomation.com/u/dturner)\
**Post date:** [March 1, 2023, 5:54pm UTC](https://forum.inductiveautomation.com/t/force-select-query-in-report/70490/3 "2023-03-01T17:54:38Z")

</div>

I will try that and see what happens. As for the update, we have purchased version 8.x but have not installed it yet. Waiting on a newer server to install it on.

---

<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:** [March 1, 2023, 6:00pm UTC](https://forum.inductiveautomation.com/t/force-select-query-in-report/70490/4 "2023-03-01T18:00:24Z")

</div>

> [@dturner](#):
>
> The system thinks the the query is an update query

That's largely because you aren't supplying a SQL _ **query** _, but a SQL _ **script** _. While some JDBC drivers tolerate this, it is not portable. Regardless, JDBC needs to know at the API level whether supplied SQL is a dataset-returning query or a result-code-returning statement. Ignition's bare query tools try to guess based on whether the SQL starts with SELECT.

Named Queries ~~, which appear in 7.9.6, IIRC,~~ allow you to specify which API to use, so the body of the SQL can be more complex.

As Paul notes, you are on an unsupported and buggy version. Some SQL **script** related bugs persist well into v8.1.x. You should avoid using SQL scripts in Ignition.

---

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://forum.inductiveautomation.com/u/dturner)\
**Post date:** [March 1, 2023, 7:05pm UTC](https://forum.inductiveautomation.com/t/force-select-query-in-report/70490/5 "2023-03-01T19:05:26Z")

</div>

The named queries worked for me. Thank you for your help!

---

<div class="post-metadata">

**Author:** ![dturner](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dturner](https://forum.inductiveautomation.com/u/dturner)\
**Post date:** [March 1, 2023, 7:08pm UTC](https://forum.inductiveautomation.com/t/force-select-query-in-report/70490/6 "2023-03-01T19:08:33Z")

</div>

Thank you for your response. I will get the opportunity to start over as soon as we get a server for our 8.x license. I will rethink how a lot of the things we do are done.

---

<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:** [March 2, 2023, 3:37pm UTC](https://forum.inductiveautomation.com/t/force-select-query-in-report/70490/7 "2023-03-02T15:37:44Z")

</div>

> [@paul-griffith](#):
>
> I _think_ 7.9.5 has named queries

You would be correct. They were introduced in 7.9.4.