Report SQL data source resulting in "Invalid column name'{....}'"

Ignition
,
1

I'm doing a project requiring a series of reports - and I haven't done any reports for a while. I've reviewed all the relevant IU videos on the reporting module as a refresher. Here's the current problem:

image
image964×222 43 KB

Figure 1. SQL data source.

image
image636×247 72 KB

Figure 2. The BatchId parameter.

Error message in Preview tab:

WARN: An error occurred while executing Query SELECT	*
FROM	HEADER
WHERE	BATCHID = "{BatchId}"	-- e.g. '001234567'
-- Invalid column name '{BatchId}'.

Can anyone suggest why it thinks '{BatchId}' is a column name?

Thanks!

2

The SQL standard uses double quotes as column and identifer delimiter. I don't believe you need the double quotes around the {BatchID} as it should take the paramter type into account. Much like parameters for Named Queries.

It might also be unhappy with the comments in the query string. I know there have been issues with that in other areas.

3

Use a ? for the parameter placeholder in the query editor, then a parameter input box will popup where you put {BatchId}

4

Thanks for the reply.

The SQL standard uses double quotes as column and identifeir delimiter.

Oops! Yes. I was experimenting on the basis of a wider web search.

I don't believe you need the double quotes around the {BatchID} as it should take the paramter type into account. Much like parameters for Named Queries.

That was my thinking too, but it's contradicted by the docs at Basic SQL Query | Ignition User Manual. (See the bit on Report Parameters in a Basic SQL Query.)

I'm continuing to investigate!

5

Doh! I found it.

image
image633×390 76.1 KB

Don't enter the {paramameter} into the query. Use the ? and an expression dialog will open below.

Thanks!

6

Did you not see my reply?

7

No, I missed it in my excitement!
I'll mark yours as the solution. Thanks!