Read Only Mode SQL Query

Ignition
1

Hello All,

I am new to Ignition so please excuse my ignorance. I am adding a project to an existing cell after taking the Core course. This cell I am adding to is a functioning cell and the project is a KPI board. I am using this as a way to learn the system and get my feet wet. The issue is that I am trying to Sum a Query from the existing database in this system. When I attempt this I get :
image

"Error running query: SUM SELECT "Holes Completed" FROM PartData_Log Cannot run this function when in read-only mode."

I have read how to fix this but I am concerned about writing to the database. This KPI should only be reading from not writing to the database.

Without the SUM syntax it doesn't create this warning. I assume it has something to do with my syntax. Could someone enlighten me as to how to do a "SUM" without needing to write to the database?

Thank You

2

  1. Where are you calling this wuery? For instance if you're calling it in a system.db.runUpdateQuery it will think its a write query even if its not.

  2. Think your query should be more like SELECT SUM(myColumn) FROM PartData_LOG, SUM should be in the SELECT clause and use the column name directly, not quoted otherwise you may be doing a SUM on a string literal.

Are you able to run SELECT SUM(mycolumn) FORM PartData_Log in the designer database browser?

3

Thanks for the quick reply. I am using the Database Query Browser to test by query:

SELECT
SUM "Holes Completed" AS Holes_Completed

FROM PartData_Log

4

Did this run in your database query browser? That syntax still doesn't look right. What database are you using (MySQL, SQL Server, etc)

5

We are using MSSQL. I have tried several methods. If I run: SELECT "Holes Completed" FROM PartData_Log It will return a long list of the correct data, I just need to SUM it

6

SELECT SUM([Holes Completed]) FROM PartData_Log is what I would expect it to look like.

7

Avoid spaces in database column names. Spaces are a constant source of issues and frustration.

Aside from that, you might want to take a look at the doc for the functions you're using...

Here, you'll need to use parentheses:

select
    sum(column) colum_name
from
    table
3 Likes
8

WORKED!! Brilliant! Thank You!!

1 Like
9

Thank You, Much appreciated

10

I would recommend if you're still in the design phase of your project to listen to his suggestion about avoiding whitespace in column names. White spaces in database column names will give you headaches that can be easily avoided with using a convention like camelCase or snake_case and then stick to that.

2 Likes
11

OK, Understood. Thanks for the help, these are really simple things getting me hung up at this point so thanks for the help.

1 Like