Gen ORA-00904 invalid identifier errors at runtime on Link Tables

Products

• Gen
• Gen - Workstation Toolset

Issue/Introduction

At runtime, the following format error can occur when using code generated with Gen 8.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

Release: 8.x

Resolution

In Gen 8.0 a new feature was introduced into the generator for Link Table optimization - this is described in the Gen 8.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 in the Issue/Introduction 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 8.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

Gen 8.0 PTF GEN8003 / RO22981