Using % on the LIKE keyword on SELECT_SQL on LANSA for Windows

Date:Archived
Product/Release:LANSA for Windows
Abstract:Special consideration when using the % wildcard character on the LIKE keyword on the WHERE clause on the SELECT_SQL
Submitted By:N/A

There is a special consideration when using the % wildcard character on the LIKE keyword of the WHERE clause on the SELECT_SQL command on LANSA for Windows: the 'LIKE' field has to be filled with % characters to the right. This issue explains why and how to do it.

LANSA for Windows does not support variable length constructs (i.e. fields). So, when a LIKE clause is evaluated, e.g. 'WHERE #GIVENAME LIKE JO%' , LANSA for Windows passes 'JO% ' (i.e. with blank characters at the right) to the embedded SQL statement (i.e. in the generated C source). This causes the SELECT_SQL command not to retrieve values like 'JOHN', 'JOHN PAUL', 'JOE', etc.

This problem is not presented on LANSA/AD because the static SQL statement, embedded into the generated RPG code, ignores the trailing blanks after the %. LANSA for Windows follows the same logic but, neither MS SQL Server nor SQL Anywhere act like RPG does. These Database Managers need a variable length field which LANSA for Windows does not support.

To overcome the situation outlined above, the LANSA function has to use a work field for the 'LIKE' field. This work field has to be built concatenating (i.e. TCONCAT) the selection field entered by the user to a field filled with % characters. The function below shows how this is done.

The sample function below uses field #GNAME to get the selection value typed in by the user, which may end with a % character. After checking that the platform is not LANSA/AD (line 32), the function puts the last character of the selection field into field #LASTCHAR. If #LASTCHAR was %, #GNAME is concatenated to a field with %s (#GNAMEX) to get the field to be used on the LIKE keyword, #GNAMEY (line 36). Then the SELECT_SQL is executed using the #GNAMEY field (note that, to be syntactically correct, in the WHERE clause, the GNAMEY field has to be prefixed by a : -a colon- instead of # -hash- (line 40). Finally, the selected records are added into a browse list and displayed.

Sample function:

FUNCTION       OPTIONS(*DIRECT);
		* *********     COMMENT(Input selection field);
		DEFINE         FIELD(#GNAME) REFFLD(#GIVENAME);
		* *********
		* *********     COMMENT(Work fields);
		* *********     COMMENT(Work field to be filled with %);
		DEFINE         FIELD(#GNAMEX) REFFLD(#GIVENAME);
		* *********     COMMENT(Work field for the WHERE clause);
		DEFINE         FIELD(#GNAMEY) REFFLD(#GIVENAME);
		* *********     COMMENT(Work field to check if the last character entered was %);
		DEFINE         FIELD(#LASTCHAR) TYPE(*CHAR) LENGTH(1);
		* *********     COMMENT(Return code);
		DEFINE         FIELD(#RETCOD) TYPE(*CHAR) LENGTH(2);
		* *********     COMMENT(Work fields to get field information);
		DEFINE         FIELD(#TYP) TYPE(*CHAR) LENGTH(1) LABEL('Type');
		DEFINE         FIELD(#LEN) TYPE(*DEC) LENGTH(5) DECIMALS(0) LABEL('Length');
		* *********
		* *********     COMMENT(List Definitions);
		* *********     COMMENT(List for Employee details);
		DEF_LIST       NAME(#LISEMP) FIELDS(#GIVENAME #SURNAME #EMPNO) ENTRYS(500);
		* *********
		* *********     COMMENT(Prepare fields if no AS/400);
		IF             COND('*CPUTYPE *NE AS400');
		USE            BUILTIN(GET_FIELD) WITH_ARGS(GIVENAME)TO_GET (#RETCOD #TYP #LEN);
		USE            BUILTIN(FILLSTRING) WITH_ARGS(%) TO_GET(#GNAMEX);
		ENDIF;
		BEGIN_LOOP;
		REQUEST        FIELDS(#GNAME);
		* *********     COMMENT(Set GNAMEY to selection field by default);
		CHANGE         FIELD(#GNAMEY) TO(#GNAME);
		* *********     COMMENT(Get field for the WHERE clause, if no AS/400);
		IF             COND('*CPUTYPE *NE AS400');
		USE            BUILTIN(RIGHT) WITH_ARGS(#GNAME Y) TO_GET(#GNAMEY);
		SUBSTRING      FIELD(#GNAMEY #LEN) INTO_FIELD(#LASTCHAR);
		IF             COND('#LASTCHAR = ''%''');
		USE            BUILTIN(TCONCAT) WITH_ARGS(#GNAME #GNAMEX) O_GET(#GNAMEY);
		ENDIF;
		ENDIF;
		CLR_LIST       NAMED(#LISEMP);
		SELECT_SQL     FIELDS(#GIVENAME #SURNAME #EMPNO) FROM_FILES((PSLMST)) WHERE('GIVENAME LIKE :GNAMEY');
		ADD_ENTRY      TO_LIST(#LISEMP);
		ENDSELECT;
		DISPLAY        BROWSELIST(#LISEMP);
		END_LOOP;