ORA-00904 Invalid identifier errors at runtime on Link Tables due to incorrect SQL

Document ID : KB000004059
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

At runtime, the following format error can occur

------------------------------------------------------------------------------   
ORA-00904: "WORKGROUP04"."ID": invalid identifier   
------------------------------------------------------------------------------  

when using code generated with Gen r8.x. 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
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 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

The variable needs to be set prior to starting the Toolset if using workstation generation - for which a noltopt.bat file has been provided - or before starting the CSE server if generation using the CSE.

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.