SELECT statement has the following syntax:
| SELECT [TOP N] select_expression [, select_expression ...] FROM dataset_reference [WHERE | BY-EXP where_condition] [ORDER BY {column_name | expression | position} [ASC | DESC]] |
- select_expression defines one or more columns of the result set.
- dataset_reference indicates table or stored procedure.
- where_condition has the syntax of [WITH] clause of jQL SELECT statement.
- [ORDER BY] together with [ASC | DESC] clause uses the functionality of jQL [BY | BY.DSND] clause.
SELECT statement has a powerful data transformation engine that can be triggered in select_expression by various functions and parameters. The following features are currently supported:
| 1. Field joins |
| 2. User Defined Functions (UDF) |
| 3. EVAL function |
| 4. Data type conversions |
| 5. Multivalue support |
| 6. Selecting fields by their numeric positions |
| 7. SUM, COUNT |
| 8. BY-EXP clause |
Field joins |
Field Joins functionality allows users to retrieve data from more than one file. This can be achieved by joining a field from the selected file with a field from another file.
Syntax:
| source_field > dest_file > dest_field |
|
source_field dest_file dest_field |
- field name of the source file, containing record ID of the target file - target file name - field name of the destination file |
Example:
| SELECT @ID AS STMT_ID, ACCOUNT_NUMBER, ACCOUNT_NUMBER>FBNK_ACCOUNT>CUSTOMER AS CUST_ID, CUST_ID>FBNK_CUSTOMER>SECTOR AS SECT, SECT>F_SECTOR>DESCRIPTION FROM F_STMT_ENTRY WHERE @ID UNLIKE 'F...' |
- Field join; assigning an alias CUST_ID - Field join with CUST_ID alias - Field join with SECT alias |
User Defined Functions (UDF) |
Users can include custom jBC functions in SELECT statements in order to obtain greater flexibility on data transformation process. jBC routine name and output parameters must be registered in Administrator tool. Here you can find more information on how to define a function.
Syntax:
| @user_function |
|
user_function |
- function name defined in Vultar Administrator tool |
Example:
| SELECT ACCOUNT_NUMBER, INPUTTER, @FIELD(INPUTTER, '_', 2, 1), AMOUNT_LCY, @AMT.DB.CR(AMOUNT_LCY) FROM F_STMT_ENTRY |
- FIELD function with one resulting column - AMT.DB.CR function returning 2 columns (DB and CR) |
EVAL function |
The driver supports jBASE EVAL function. The function may be used as part of select_expression, or inside of where_conditions. Please note, EVAL function works slower than direct selection of fields or user defined functions. Whether data processing speed is crucial, consider using UDF instead.
Syntax:
| EVAL(eval_expression) | - as part of select_expression |
| EVAL'eval_expression' | - as part of where_conditions |
|
eval_expression |
- an I-TYPE expression |
Being part of where_conditions, EVAL syntax must be jQL compatible, including name of the fields. As a select_expression, EVAL can also accept column aliases defined in preceding select_expression items.
Example:
| SELECT ACCOUNT_NUMBER AS ACCT, EVAL(IF ACCOUNT_NUMBER[1, 3] = 'EUR' THEN 'INT' ELSE 'CUS'), EVAL(IF ACCT[1, 3] = 'EUR' THEN 'int' ELSE 'cus') AS ALIAS FROM F_STMT_ENTRY WHERE EVAL'ACCOUNT.NUMBER[1, 3]' NE 'USD' |
Data type conversions |
By default, the driver delivers character data type for any jBASE field. However, users may alter the result set by converting one data type into another.
Syntax:
| select_expression ; conv_code [ .param ] |
|
select_expression conv_code param |
- any select expression. - conversion code. See below the list of available codes. - optional parameter of the conversion code specified. |
Conversion codes:
| C.n | Character | Ex: C.200 - expanding field size to 200 characters. |
| D.mask | DateTime | Ex: D.”YYYYMMDD” (this is the default mask. Can be ignored.) |
| $ | Currency | |
| F | Float | |
| I | Integer |
Example:
| SELECT TOP 10 @ID, WORKING_BALANCE AS WB_STR, WORKING_BALANCE;F AS WB_CCY, DATE_TIME, DATE_TIME;D."YYMMDDHHNN" FROM F_ACCOUNT WHERE WORKING.BALANCE NE '' |
- CHAR field type - convert it to FLOAT (numeric) type - convert to DATETIME, specifying the mask. |
Multivalue support |
SELECT statement delivers data in two-dimensional dataset. By default, the content of each multivalue field will be displayed as a VM/SM delimited string value. However, there is a number of powerful built-in features implemented, which may help read and process multivalued data. With these features, ETL tools can easily prepare output datasets for external systems with master/detail relationships that will help developers build complex SQL requests on multivalued fields. On the other hand, multivalue support in SELECT statement provides developers enough flexibility to use jBASE data source directly for online reporting and enquiry needs, thus avoiding upload of data to external databases.
Find below a list of parameters that are applicable on multivalue fields in select_expression:
| # | Description | Syntax |
| 1 | Reading a particular value/subvalue | field_name[value{,subvalue}] |
| 2 | Summarizing values/subvalues within a field | field_name[{n,}{EX,}+] |
| 3 | Replacing @VM and @SM with spaces | field_name[0{,0}] |
| 4 | Expanding a multivalue field into separate records | field_name[EX{,EX}] |
| 4.1 | Returning value position within expanded field | field_name[VN] |
| 4.2 | Returning subvalue position within expanded field | field_name[SN] |
| 5 | Returning the value of a multivalue field located with BY-EXP clause | field_name[#] |
Examples:
The following screenshot displays the record of a T24 application that contains several multivalue fields. Some of them have subvalues as well (ex. PAY.TYPE, PAY.AMT.ORIG...). Field values grouping is based on a hardcoded logic of the application (fields 24-35).
1. Selecting a record with two fields PAYMENT_DTE_DUE and PAYMENT_AMOUNT. We will not apply any data conversion/transformation, just displaying default data structure.
|
SELECT PAYMENT.DTE.DUE;C.100, PAYMENT.AMOUNT;C.100
FROM FBNK.PD.PAYMENT.DUEWITH @ID = 'PDLD0627400304' |
2. Expanding multivalue fields PAYMENT_DTE_DUE and PAYMENT_AMOUNT. Adding field VN that returns current value position of the selected field.
| SELECT PAYMENT.DTE.DUE[VN] AS VN, PAYMENT.DTE.DUE[EX], PAYMENT.AMOUNT[EX] FROM FBNK.PD.PAYMENT.DUE WITH @ID = 'PDLD0627400304' |
3. Selecting additional two multivalue fields that have subvalues (PAY_TYPE and PAY_AMT_ORIG)
| SELECT PAYMENT.DTE.DUE[VN] AS VN, PAYMENT.DTE.DUE[EX], PAYMENT.AMOUNT[EX], PAY.TYPE[EX], PAY.AMT.ORIG[EX] FROM FBNK.PD.PAYMENT.DUE WITH @ID = 'PDLD0627400304' |
4. Expanding subvalue fields:
| SELECT PAY.TYPE[VN] AS VN, PAY.TYPE[SN] AS SN, PAYMENT.DTE.DUE[EX];D, PAYMENT.AMOUNT[EX], PAY.TYPE[EX,EX], PAY.AMT.ORIG[EX,EX];F FROM FBNK.PD.PAYMENT.DUE WHERE @ID = 'PDLD0627400304' |
Selecting fields by their numeric positions |
If a particular file does not have dictionary, or file dictionary does not contain some field definitions, users can still access "missing" fields by using *A<n> syntax. The driver will try to find a field reference in dictionary with the same numeric position as specified in SELECT statement in order to assign a proper name to the resulted field. In case no dictionary items are present with such value, the resulted field will be named FIELD_<n>.
Syntax:
| *A<n> |
|
<n> |
- absolute numeric position of the field in a file |
Example:
| SELECT *A1, MNEMONIC, *A2 AS CATEG_ALIAS, *A175 FROM F_ACCOUNT WHERE *A1 NE '' ORDER BY "*A1" |
- first field; the dictionary has a ’D’ item CUSTOMER with position 1 - second field; assigning a name alias - there are no items in the dict. with position 175. Using name FIELD_n - use double quotation marks in ORDER BY clause. |
SUM, COUNT |
SELECT statement supports SUM and COUNT functions. The SUM function returns the total sum of a numeric column. The COUNT function returns the number of resulted values of the specified column.
Syntax:
| SUM(field_name) | |
| COUNT(field_name) |
Example:
| SELECT SUM(WORKING_BALANCE), COUNT(*) FROM F_ACCOUNT WHERE WORKING_BALANCE NE '' |
BY-EXP clause |
SELECT statement supports BY-EXP clause. Use <field_name[#]> syntax to compute values located with BY-EXP clause.
|
LIST FBNK.PD.PAYMENT.DUE PAYMENT.AMOUNT BY-EXP PAYMENT.DTE.DUE = "20070615" SAMPLE 10 @ID............................ PAYMENT.AMOUNT..... PDLD0615300002 132.7 3 Records Listed |
Example:
| SELECT TOP 10 @ID, PAYMENT.AMOUNT[#];F FROM FBNK.PD.PAYMENT.DUE BY-EXP PAYMENT.DTE.DUE = '20070615' |
