SAND CDBMS Tools Reference Guide
Data Loader (ndlm)

 

Previous Topic:
Import Map Specification
Chapter Index
Next Topic:
Declaring Variables in NDL++ Scripts

 

The NDL++ Data-Load Scripting Language


Introduction to NDL++

NDL++ is a scripting language that enables the analysis, manipulation, and conversion of data as it is loaded into a database.

The NDL++ language provides both character string and math functions. It can perform data-type conversion and data analysis of specified fields, and can also generate certain kinds of data for insertion into the target table (including current date and time as well as a unique key for a specified group of fields). Variables can be declared to receive the output of data manipulation or analysis; these variables can then be used in statements or expressions elsewhere in the script. Conditional expressions can be used to specify courses of action that are to be taken only when certain conditions are present.

The NDL++ String Functions

Character string functions can be used in the map specification (table-name) section and in the NULLIF, MISSINGIF, and SKIPIF clauses of ndlm import specification script files. They are typically included in the map specification to specify operations to be performed on input data. Analytical functions are useful for inclusion in variables and conditional expressions.

The following table lists in alphabetical order the character string functions that can be used in NDL++ scripts, and provides a short description of each function along with its input and output data types. More detailed descriptions and examples are included in the String Function Syntax and Examples section.

Function Description Input Data Type Output Data Type
CMP() Compares the ASCII values of two strings char int 
DATE() Returns the current date in a specified format  char
ENFTONF() Converts a European Number (ENF) string to a Standard Number (NF) string char char
EXTRACTWORD() Searches the input string for the nth occurrence of a substring, returning the substring if found, or an empty string otherwise char and int char
GENKEY() Generates a unique key, 19 characters in length (RECORD section field input) char char
GENKEYCOL() Generates a unique key, 19 characters in length (mapping section column input) char char
INSTR() Determines the position of the first character of the first occurrence of one string within another char int
INSTRREV() Determines the position of the last character of the last occurrence of one string within another char int
ISENF() Determines whether an input string is a valid European Number (ENF) char int
ISNULL() Determines whether a column contains a null value char int
ISNUMERIC() Determines whether a string is numeric char int
LCASE() Converts a string to lowercase characters char char
LEFT() Returns a specified number of characters from the left side of a string char char
LEN() Returns the length of a string char int
LOOKUP() Searches for a key value in either a table, the output of an SQL statement, or the fields in the RECORD or table section of a specification file, returning the value from a field in the same row as the first match if the key value is found, or a user-specified default value otherwise char char, int, or float
LPAD() Adds a specified character to the left side of a string to achieve a specified length char char
LTRIM() Removes all instances of a specified character from the left side of a string char char
MID() Returns a specified number of characters from a string char char
NFTOENF() Converts a Standard Number (NF) string to a European Number (ENF) string char char
PAD() Adds a specified character to both sides of a string to achieve a specified length char char
REPLACE() Finds and replaces a substring within a string char  char
RIGHT() Returns a specified number of characters from the right side of a string char char
RPAD() Adds a specified character to the right side of a string to achieve a specified length char char
RTRIM() Removes all instances of a specified character from the right side of a string char char
SPACE() Returns a string consisting of a specified number of spaces int char
STR() Converts a numeric value into a string value int or float char
STRENF() Converts an int or float input value to a European Number (ENF) string int or float char
STRING() Returns a string consisting of a specified character repeated a specified number of times char char
STRREV() Reverses a string char char
TIME() Returns the current time in a specified format   char
TRIM() Removes all instances of a specified character from the left and right sides of a string char char
UCASE() Converts a string to uppercase characters char char
VALENF() Converts a European Number (ENF) string to a float value char float
VALENI() Converts a European Number (ENF) string to an int value char int
VALF() Converts a string containing numeric characters into a float value char float
VALI() Converts a string containing numeric characters into an integer value char int

The NDL++ Math Functions

Math functions can be used in the map specification (table-name) section and in the NULLIF, MISSINGIF, and SKIPIF clauses of ndlm import specification script files. The following table lists in alphabetical order the math functions that can be used in NDL++ scripts, and provides a short description of each function along with its input and output data types. More detailed descriptions and examples are included in the Math Functions Syntax and Examples section.

Function Description Input Data Type Output Data Type
ABS(x) Calculates the absolute value of x int or float  same as x
ATN(x) Calculates the arctangent of x in radians int or float  same as x
COS(x) Calculates the cosine of x in radians int or float  same as x
EXP(x) Calculates the exponential of int or float  float
LOGN(x) Calculates the natural logarithm of x int or float  float
POW(x,y) Calculates x raised to the power of y int or float  int or float
ROUND(x) Rounds x to the decimal point int or float  int
SIGN(x) Determines the sign of x: returns 1 if x is positive, 1 if x is negative, and 0 if x is zero. int or float  int
SIN(x) Calculates the sine of x in radians.  int or float float
SQR(x) Calculates the square root of x int or float  float
TAN(x) Calculates the tangent of x in radians int or float  float
TRUNC(x) Truncates x at the decimal point int or float  int

 

Data Type Declaration in NDL++ Scripts

By default, data references in NDL++ scripts are considered to be of character type. When a column or field represents a numeric value (that is, FLOAT or INTEGER), the data type must be declared, either within an optional TYPE section (as in Example 1 below), or when referencing the field in an expression (as in Example 2).

If included, the TYPE section precedes the RECORD section (and the VAR section if this is present). Type declarations in a TYPE section are enclosed by braces ( { } ).


Data Type Declaration Syntax

TYPE
{
data-type field-name,field-name1,...field-nameN
...
}

or, when declared with an expression elsewhere in the script:

data-type(field-name)

The data-type entry may be either FLOAT or INT.


Examples

Example 1:

input: -5,5,0.55


IMPORT @sample.dat
{

TYPE
{
INT field1, field2
FLOAT field3

}
RECORD
{
field1 *,
field2 *,
field3 *\r\n
}
schema1.table1
{
col1 field1+field2+field3
}

}


Results:

schema1.table1.col1 0.55


Example 2:

input: -5,5,0.55


IMPORT @sample.dat

{

RECORD
{
field1 *,
field2 *,
field3 *\r\n
}
schema1.table2
{
col1 INT(field1)+INT(field2)+FLOAT(field3)
col2 ABS(INT(field1))
col3 ABS(INT(field1)*INT(field2))

}

}


Results:

schema1.table2.col1 0.55
schema1.table2.col2 5
schema1.table2.col3 25

 
Previous Topic:
Import Map Specification
Chapter Index
Next Topic:
Declaring Variables in NDL++ Scripts