This content has been marked as final. Show 5 replies
This is probably because the Desktop user does not have the privileges to run a function called from the SQL. The function will not be recognised causing the Invalid Identifier exception.
In Plus, either there is a different user or the SQL generated is different.
Does the error message say what is the invalid item?
You probably need to trace the session to find the offending SQL statement.
When I run the report in DESKTOP I get that the field i436274 is unidentified.
When I check the sql, I see that it is referring to:
"MAX(i436274) as as436122_436326_NEW,"
which is never referenced. I tried looking through the fields in the tables, but could not find "max" in any of the fields.
When the report is run in PLUS, the following max statement exists in the sql (without an error):
"MAX(o436065.CODE_GOAL) as as436122_436326_NEW,"
There exists a field "code_goal group" with the formula:
"CASE WHEN Yes No = 'yes' THEN Code Goal ELSE '9999999999' END"
I saw that there was no "max" here, but now that I am writing this to you, I am wondering if the problem is that the field was named "code_goal group" without an underscore between goal and group. Is it possible that in PLUS the Discoverer is interpreting the field as if the field "code_goal" is some kind of group??
The MAX is included because Discoverer has to aggregate the data because a group sort is used or a group function such as MAX has been included in an item or calculation.
You could well find that this problem is caused by using key word such as GROUP in the names of your variables, especially if you also use CASE in a calculation. Discoverer has to scan the calculation looking for the key words used in the case statement and gets confused if it finds key words in the variable names. Try using underscore to ensure the variable is treated as one word.
Another thing to try is changing the SQLFlatten and SQLType preferences in the registry for Discoverer Desktop. If you change SQLType to 2 it will show the query exactly as it was sent to the database. If you set SQLFlatten to 0 it will not attempt to optimise the SQL before it is sent to the database.
Okay. Thank you very much for the information. I am going to have check with the "decision makers" around here what they would like to do.
Since the report has been around for a good couple of years, I don't know what the original intention was with the "group" in the field name. Being that the report does run (apparently correctly) in PLUS, it might just be left as is and changes will be made only in PLUS. In general, most of the users do not use DESKTOP since Oracle is no longer developing in it. If any user who does have DESKTOP encounters a problem, he will have to just switch to PLUS.
Once again, thank you very much.