7.109.2 SELECT_SQL Column Names versus Column Values

The basic rule is:

         if a name is preceded by a '#' it means the Column Name should be used

         if its preceded by a ':' it means the Column Value should be used.

The only exception to this is in the WHERE and HAVING parameters when the #Field is the ONLY value in the parameter. In that case, it means use the Column Value.

The secondary rule is that if an identifier does not have a '#' or ':' then it will be interpreted as a column name unless it is also an SQL Name, in which case it will be left exactly as typed in the generated code. The Visual LANSA Editor will display the following warning if this is the case:

     Ambiguous. Token <name> is an SQL keyword and a LANSA field. If it's a LANSA field, prepend a '#' to the field.

Following are some examples to help explain it more fully.

When using a column name that is an SQL keyword, LANSA will not convert it. So it must be specified explicitly as either the mangled name, LANSA [long] Name, depending on how the table has been created. E.g. #SECTION. This is mangled to S_CTION and its Name may be set to SectionCode.

If the file is using mangled names or Names then this code will not work:

     Select_Sql Fields(#SECTION) From_Files((PSLMSTX2)) Group_By(SECTION)
     Add_Entry
     Endselect

The SQL would be:

SELECT "SectionCode" FROM "EVDEXLIB"."PersonnelMaster2" GROUP BY "SECTION"

The Group_By(SECTION) will be left as it is, which will not match the actual column name - S_CTION or SectionCode.

To fix this code in the most flexible manner (See Note 2 following) prepend a '#' to the name as in:

     Select_Sql Fields(#SECTION) From_Files((PSLMSTX2)) Group_By(#SECTION)
     Add_Entry

Endselect

The SQL  for this would be:

SELECT "SectionCode" FROM  "EVDEXLIB"."PersonnelMaster2" GROUP BY  "SectionCode"

But if the field name is not an SQL keyword like EMPNO here, it WILL automatically convert the Field name to the actual column name, with or without the '#':

     Select_Sql Fields(#EMPNO) From_Files((PSLMSTX2)) Group_By(EMPNO)
     Add_Entry

Endselect

If the Name for EMPNO is EmployeeNumber, and PSLMSTX2 allows Names (i.e. long names) then the Group_By EMPNO will be resolved to EmployeeNumber. The SQL would be:

     SELECT "EmployeeNumber" FROM "EVDEXLIB"."PersonnelMaster2" GROUP BY "EmployeeNumber"
Note 1: '#Field' means use the column name in the generated SQL in all parameters except when it's the ONLY value in WHERE or HAVING, in which case it will generate the runtime contents of the Field. The Free Format version of SELECT_SQL - the USING parameter - is not included in this. It has its own semantics described in SELECT_SQL Free Format Parameters.
Note 2: 'Flexible manner' in the sense that if the old style Windows mangling of column names is being used then the generator will automatically use 'S_CTION' for Windows & Linux and 'SECTION' on IBM i.
  • No labels