CA Gen ORA-00904 invalid identifier errors at runtime on Link Tables due to incorrect SQL
Article ID: 4059
Updated On:
Products
Issue/Introduction
At runtime, the following format error can occur when using code generated with Gen r8.x.
------------------------------------------------------------------------------ ORA-00904: "WORKGROUP04"."ID": invalid identifier ------------------------------------------------------------------------------
Inspection of the code will reveal that the invalid identifier in question is used in the WHERE clause, but not declared in the FROM clause in the generated SQL.
SELECT DISTINCT
ITEM01.NAME,
ITEM01.ID
FROM
ITEM ITEM01,
WORKED_BY WORKED_BY02,
MEMBERSHIPS_OF MEMBERSHIPS_OF03
WHERE
(
WORKED_BY02.FK_ITEMID = ITEM01.ID AND
WORKED_BY02.FK_WORKGROUPID = WORKGROUP04.ID AND
MEMBERSHIPS_OF03.FK_WORKGROUPID = WORKGROUP04.ID
AND MEMBERSHIPS_OF03.FK_INDIVIDUALID = :id_001ef
)
ORDER BY ITEM01.NAME ASC
Environment
Component:
Resolution
In Gen r8.0 a new feature was introduced into the generator for Link Table optimization - this is described in the Gen r8.0 Release Notes. In some situations this can produce SQL with incorrect syntax. Take the following example.
For a data model like this:
Entity Type | +-INDIVIDUAL
Attribute | | I ID (Text, 8, Mandatory, Basic)
Attribute | | NAME (Text, 40, Mandatory, Basic)
Relationship | | Sometimes IS_A_MEMBER_OF One or More WORKGROUP
| +-
Entity Type | +-WORKGROUP
Attribute | | I ID (Number, 5, Mandatory, Basic)
Attribute | | NAME (Text, 40, Mandatory, Basic)
Relationship | | Sometimes HAS_MEMBERSHIPS_OF One or More INDIVIDUAL
Relationship | | Sometimes WORKS One or More ITEM
| +-
Entity Type | +-ITEM
Attribute | | I ID (Number, 3, Mandatory, Basic)
Attribute | | NAME (Text, 15, Mandatory, Basic)
Relationship | | Sometimes IS_WORKED_BY One or More WORKGROUP
| +-
and an action block with the following read statements:
+=>READ individual ! ! WHERE DESIRED individual id = in id ! +> WHEN successful ! ! +=>READ EACH (Distinct) item ! ! ! TARGETING group_export FROM THE BEGINNING UNTIL FULL ! ! ! SORTED BY ASCENDING item name ! ! ! WHERE DESIRED item is_worked_by SOME workgroup ! ! ! AND THAT workgroup has_memberships_of CURRENT individual ! ! ! MOVE item TO out_rg item ! ! +-- ! +> WHEN not found +-
the SQL shown above will be produced.
In order to prevent this situation a feature has been introduced where the Link Table optimizer can be turned off. This was introduced with Gen r8.0 PTF GEN80003 (or Encyclopedia equivalent) and is implemented via a system variable. The environment variable name is GENLTOPT and if set to N will disable the optimizer. This will result in the following code for this specific example:
SELECT DISTINCT
ITEM01.NAME,
ITEM01.ID
FROM
ITEM ITEM01,
WORKGROUP WORKGROUP04,
WORKED_BY WORKED_BY02,
MEMBERSHIPS_OF MEMBERSHIPS_OF03
WHERE
(
WORKED_BY02.FK_ITEMID = ITEM01.ID AND
WORKED_BY02.FK_WORKGROUPID = WORKGROUP04.ID AND
MEMBERSHIPS_OF03.FK_WORKGROUPID = WORKGROUP04.ID
AND MEMBERSHIPS_OF03.FK_INDIVIDUALID = :id_001ef
)
ORDER BY ITEM01.NAME ASC
If using workstation generation the environment variable GENLTOPT needs to be set to N prior to starting the Toolset and a noltopt.bat file has been provided to both set the variable and start the toolset.
Similarly if using CSE generation the environment variable GENLTOPT needs to be set to N before starting the CSE (for Windows CSE running as a service set GENLTOPT as a System level environment variable for the CSE Service CSESvcMD to pick up)
It is recommended to only turn the optimizer off for those action blocks that encounter problems so as to maximize the benefit that the optimizer brings in terms of performance.
Additional Information
https://support.ca.com/us/download-center/solution-detail.html?docid=531894&os=WINDOWS&aparno=RO22981&actionID=5
Feedback
