Using % on the LIKE keyword on SELECT_SQL on LANSA for Windows
|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|
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.
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;