Using SELECT_SQL WHERE with negative numbers

Date:Archived
Product/Release:LANSA for Windows
Abstract:When the SELECT_SQL's WHERE clause uses negative numbers, the sign has to be at the front. Here is a work around.
Submitted By:N/A

Negative numbers in LANSA for Windows have the minus sign at the end. This is a problem because SQL Anywhere, as well as MS SQL Server, expect that negative numbers have the minus sign at the front. Therefore, the negative values are not interpreted correctly on the SELECT clauses (e.g. WHERE).

The following function shows a work around to overcome this situation. The idea is to convert the negative number to a character field with the sign at the front. The function REQUESTs two values (viz. From and To) to be used as the selection criteria on the WHERE clause. Note also the use of leading colons (i.e. :WRKF1 and :WRKT1) when referring to the function variables.

FUNCTION OPTIONS(*DIRECT);
* ********* COMMENT(From and To fields);
DEFINE FIELD(#WRKFROM) TYPE(*DEC) LENGTH(15) DECIMALS(2) EDIT_CODE(K);
DEFINE FIELD(#WRKF1) TYPE(*CHAR) LENGTH(20);
DEFINE FIELD(#WRKTO) TYPE(*DEC) LENGTH(15) DECIMALS(2) EDIT_CODE(K);
DEFINE FIELD(#WRKT1) TYPE(*CHAR) LENGTH(20);
* ********* COMMENT(Work fields);
DEFINE FIELD(#WRKPOS) TYPE(*DEC) LENGTH(15) DECIMALS(2);
DEFINE FIELD(#WRKX) TYPE(*CHAR) LENGTH(20);
* ********* COMMENT(Request From and To Fields);
REQUEST FIELDS(#WRKFROM #WRKTO);
* ********* COMMENT(Conversion of From field to characters);
IF COND('#WRKFROM *LT 0');
CHANGE FIELD(#WRKPOS) TO('(#WRKFROM * -1)');
USE BUILTIN(NUMERIC_STRING) WITH_ARGS(#WRKPOS) TO_GET(#WRKX);
USE BUILTIN(TCONCAT) WITH_ARGS('-' #WRKX) TO_GET(#WRKF1);
ELSE;
USE BUILTIN(NUMERIC_STRING) WITH_ARGS(#WRKFROM) TO_GET(#WRKF1);
ENDIF;
* ********* COMMENT(Conversion of To field to characters);
IF COND('#WRKTO *LT 0');
CHANGE FIELD(#WRKPOS) TO('(#WRKTO * -1)');
USE BUILTIN(NUMERIC_STRING) WITH_ARGS(#WRKPOS) TO_GET(#WRKX);
USE BUILTIN(TCONCAT) WITH_ARGS('-' #WRKX) TO_GET(#WRKT1);
ELSE;
USE BUILTIN(NUMERIC_STRING) WITH_ARGS(#WRKTO) TO_GET(#WRKT1);
ENDIF;
* ********* COMMENT(Select using From and To character fields);
SELECT_SQL FIELDS(#FLDA #FLDB #FLDC) FROM_FILES((FILEA)) 
WHERE('(FLDB >= :WRKF1) and (FLDB <= :WRKT1)');
DISPLAY FIELDS(#FLDA #FLDB #FLDC);
ENDSELECT;