Hi Jmix team,
There was an issue that happened as shown from the image above, because there was a commented SQL line in the report band query.
Originally, the query had 9 parameters. After commenting out one condition, the active SQL should only have 7 parameters. However, when the report was executed through the report runner, it still attempted to pass all 9 parameters, causing the error:
ORA-17003: Invalid column index
Even though the line was commented, the report runner/Jmix still appeared to recognize the parameter placeholders and attempted to bind them.
Not sure whether the report runner properly ignores commented SQL sections, or if it simply scans the entire query string for parameter placeholders.
P.S. There wont be any issue if the commented SQL line does not contain any parameterized fields (eg. ${USER_ID}
you may want to check the Parameters tab. If the parameter is defined there, it can be set as required or not by a checkbox. Try to set “not required” on the commented parameter in the SQL.
Yes regarding that we realized that there is a checkbox for us to set whether a parameter is “required” or not. However, my ${USER_ID} parameter is already set to not required when the issue happens. Could you please check again on this matter?
Hello,
just realized something: ORA-17003: Invalid column index
That is your database error, saying that you are using a column name that is not in the select SQL, or maybe your report template has more or fewer columns than the SQL.
I recommend deleting the commented SQL line, checking your SQL, deleting the parameter in the Parameters tab, and then cross-referencing this to the report template.
I will play later in the day if you still have a problem, or maybe somebody else knows the answer before that.
Also, I often use the Table template to develop the query. This way I don’t need a real template as this one does not have a predefined list of columns, it creates them dynamically. So, add a Table template query to your report so you can experiment.
The Required checkbox does not affect this case. It only controls whether the report parameter may be empty, but it does not prevent ${...} placeholders from being processed in the SQL dataset.
From your example it looks like the report engine still detects ${USER_ID} inside the commented SQL line:
-- AND (GL06BATCH.crt_by = ${USER_ID} or ${USER_ID} is null)
Even though this line is commented from the SQL point of view, the report parameter parser may still find ${USER_ID} in the query text and try to bind it. This can lead to a parameter count mismatch and then to:
ORA-17003: Invalid column index
So for now, please don’t leave ${...} report parameters inside commented SQL lines. Remove the line completely, or change the comment so that it does not contain ${USER_ID}.
For optional filtering, you can keep the condition active and handle null values explicitly, for example:
AND (${USER_ID} is null OR GL06BATCH.crt_by = ${USER_ID})
We’ll check whether the parser can be improved to ignore placeholders inside SQL comments.
