Using LANSA fields in SELECT_SQL WHERE parameter

Date:Archived
Product/Release:LANSA - All Platforms
Abstract:The current implementation of SELECT_SQL does not translate LANSA fields in the WHERE( ) parameter
Submitted By:LANSA Technical Support

The current implementation of SELECT_SQL does not translate LANSA fields in the WHERE( ) parameter (even though the help text/manual shows this method being used).

There is a work around for this problem. When a colon (:) is put in front of the field name, it means that the value of the RPG field name should be used. As LANSA uses the actual name of the field in the produced RPG (if it is less than or equal to 6 characters), putting a colon in front of the LANSA field will have the same effect as having LANSA translate the field itself.

Here is an example:

DEFINE #KARTIC REFFLD(#ARTICO)
DEFINE #KADTRG REFFLD(3MADTRG)
DEF_LIST #L (#ARTICO #MADTRG)

BEGIN_LOOP
REQUEST (#KARTIC #KADTRG) BROWSELIST(#L)
CLR_LIST #l
SELECT_SQL (#ARTICO #MADTRG) FROM_FILE(MGM) WHERE ('ARTICO = :KARTIC AND MADTRG > :KADTRG') ORDER_BY('ARTICO, MADTRG')
ADD_ENTRY #L
ENDSELECT
END_LOOP

Notice that the LANSA fields in the WHERE( ) parameter have colons (:) before them instead of hashes (#). This will work if the LANSA fields to be translated have 6 character or less in their names. If longer field names are being used, smaller work fields will have to be used in the WHERE( ) parameter.

For example :

DEFINE #KEYARTICO REFFLD(#ARTICO)
DEFINE #KEYMADTRG REFFLD(#MADTRG)
DEFINE #WORK1 REFFLD(#MADTRG)
DEFINE #WORK1 REFFLD(#ARTICO)
DEF_LIST #L (#ARTICO #MADTRG)
	
BEGIN_LOOP
REQUEST (#KEYARTICO #KEYMADTRG) BROWSELIST(#L)
CLR_LIST #L
CHANGE #WORK1 #KEYARTICO
CHANGE #WORK2 #KEYMADTRG
SELECT_SQL (#ARTICO #MADTRG) FROM_FILE(mgm) WHERE ('ARTICO = :WORK1 AND MADTRG > :WORK2) ORDER_BY('ARTICO, MADTRG')
ADD_ENTRY #L
ENDSELECT
END_LOOP

Small work fields are used in the above WHERE( ) parameter because #KEYARTICO and #KEYMADTRG would have to be renamed inside the RPG created for the function.

Note for LANSA for Windows users:

LANSA for Windows generates C code instead of RPG. C allows large field names so this work-around can be done without having to worry about 6 character field names if LANSA for Windows is used. However, it is better to use 6 character field names in the WHERE so that the same SELECT_SQL command will work on both platforms.