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;