SQL iQuery Script Built-in Function

Index

isEmpty / IsNotEmpty

Is the specified iQuery Script Session Variable not Defined or Defined and Empty or Defined and Not Empty?

The ISEMPTY() built-in functions returns true if the SQL iQuery Script Session Variable exists and it has no value assigned to it (it is empty), otherwise it returns false.
The ISNOTEMPTY() built-in function returns true if the SQL iQuery Script Session Variable exist and has a value assigned to it. If the variable is not defined or is defined but has no value assigned to it, it return false.

Syntax

The ISNOTEMTPY() built-in function can be used to determine if a Session Variable Exists and contains data. Use this when writing dynamic WHERE clause on your SQL SELECT statement--omitting conditions when the user did not specify a value.
The ISEMPTY() built-in function can be used to determine what to do when a Session Variable Exists and is empty or does not exist. For example, you may wish to assign a default value to the variable if it is empty.

Parameters

  1. The name of an iQuery Session Variable

#default &Company = 01
#define  &reg  = ''

if IsNotEmpty(&reg);
#h2 Region &Reg
endif;
if isEmpty(&Company);
  eval &Company = 99;
endif;

select * from foo F
#IF isNotEmpty(&REG)
  WHERE F.region = '&reg'
#endif

The two Session Variables &COMPANY and &REG are declared. &COMPANY is initialized to 01, while &REG is empty. The IF ISNOTEMPTY(&REG) returns false since although &REG is defined, it contains no value. The IF ISEMPTY(&COMPANY) will return false because &COMPANY was initialized to 01.
Next a WHERE clause is added to the SELECT statement if the &REG variable is defined and contains a value. If it doesn't, then no WHERE clause is added to the SELECT statement. Note the user of the #IF instead of the regular IF statement. # commands can be embedded within an SQL statement to provide control over the creation of that statement. Regular non # command, like IF, ELSE, and ENDIF would be combined with the SELECT statement since the parser won't see them as different statements since SELECT and any SQL statement may span multiple source lines.