Ignition Report SQL Query in v7.9 Convert to v8.1.25

I have a working Data Source in my Ignition v 7.9 that is a SQL Query. I am trying to convert it into a functioning Report in my other Ignition v8.1.25. Below is the script that works in v7.9.

> SELECT CONVERT(date,FATS_Land.t_stamp) AS 't_stamp',
  FATS_Land.Crusher_DailyTotal,
  FATS_Land.FRE_WIN_UTIL_FATS_EFFLUENT_RANCH_DAILY_FLOW,
  FATS_Land.Val_City_Byp_Yest_Total,
  FATS_Land.FRE_WIN_UTIL_FATS_EFFLUENT_POTW_DAILY_FLOW,
  FATS_Land.DB_Sewer_City_Day_Old
FROM FATS_Land
WHERE Year(FATS_Land.t_stamp) = ? AND Month(FATS_Land.t_stamp) = ?

When I write this query in v8.1.25 it throws the following error. What is the issue with the "?"

> Error running query: SELECT CONVERT(date,t_stamp) AS 't_stamp', Crusher_DailyTotal, FRE_WIN_UTIL_FATS_EFFLUENT_RANCH_DAILY_FLOW, Val_City_Byp_Yest_Total,FRE_WIN_UTIL_FATS_EFFLUENT_POTW_DAILY_FLOW,DB_Sewer_City_Day_Old FROM FATS_Land WHERE YEAR(t_stamp) = ? AND MONTH(t_stamp) = ? Incorrect syntax near '?'.

Any help will be greatly appreciated.

Did you use the right type of data source?
For ? placeholders to work you must use 'SQL Query', not 'Named Query' or 'Basic SQL Query' each of which work subtly differently.

1 Like

Yes, it is set for SQL Query.

I have tried both "Universal" and "MSSQL", neither of the work.

And you're substituting parameters in the expression popups below, right? Can you show us the entire data configuration window as you have it in the software?

I don't know what is wrong, but I can't help noting that your WHERE clause is horrible--it can never be optimized with indices, and will become terribly slow as your table grows in size.

Your WHERE clause should be comparing the bare t_stamp column to lower and upper boundary timestamps.

@pturmel , Thank you for your kind words about "my" WHERE clause. :slight_smile:

This was written by someone else about 10 years ago. I am just here getting things migrated to v8.

This still does not answer my question about the error on "?".

Is there a different syntax to use in v8 ?

To expand on Phil's reply,

This syntax requires that every single record in the data table must be examined, the YEAR function run and the MONTH function run. Any index on the column can't be used.

Instead, for MySQL, use something like this.

WHERE 
    FATS_Land.t_stamp BETWEEN 
        STR_TO_DATE(CONCAT(?, '-', ?, '-', 01), '%Y-%m-%d')
        AND
        DATEADD(
            STR_TO_DATE(CONCAT(?, '-', ?, '-', 01), '%Y-%m-%d'),
            INTERVAL 1 MONTH
        )

Note that you'll have to pass the year and month values in twice.
This way the required date range endpoints are converted to date types just once and the index can now be used to home in on the records of interest very quickly.

1 Like

No, the syntax should be the same.

Are you literally copying and pasting the report, or how are you updating/upgrading to 8.1? If you export the project/resource from 7.9 and import into the 8.1 gateway, this is what you get?

Reporting is essentially identical between 7.9 and 8.1, so I really don't think the upgrade is implicated here other than coincidentally.

@PGriffith
Thank you for confirming that the syntax is the same.

I have two installations of Ignition. One is v7.9 and the other is v8.1. I literally copied the query from v7.9 project and pasted it into the v8.1 project. I am NOT upgrading the v7.9 application.

I think I can figure it out from here.

Kindest regards,
Jeremy

Try exporting the resource out of the designer in 7.9 and importing into 8.1, just to see if that works. I would expect copying and pasting to work, but it'd be curious if the import works correctly...

2 Likes