In the Column Map Proc Editor you can create, modify, or delete Column Map Procedures as objects stored in the Optim™ Directory or defined within a Column Map. You can also export a file that contains information needed to create a Column Map Procedure or import a similar file in order to create a Column Map Procedure.
Text to describe or explain the purpose of the Column Map Procedure (up to 40 characters).
When you open the editor, the window displays either:
Intended for use as a guideline, the template Column Map Procedure includes the following native functions:
Function | Description |
---|---|
The Load function can be included in a Column Map Procedure. This optional function is called before any tables are processed. You can use this function for initialization or other tasks that apply generally in the procedure. | |
Function CmStartTable () As Integer | The CmStartTable function can be included in a Column Map Procedure. This optional function is called before each table is processed. You can use this function for initialization or other tasks that affect or apply to each table processed in the procedure. |
Function CmTransform () As Integer | The CmTransform function must be included in a Column Map Procedure. This required function is called for each row that is processed and provides instructions for the data transformation to be performed. |
Function CmEndTable | Only one instance of the CmEndTable function can be included in a Column Map Procedure. This optional function is called after each table is processed. You can use this function for tasks required after each table is processed. |
Sub Unload() | Only one instance of the Unload function can be included in a Column Map Procedure. This optional function is called after all tables are processed. You can use this function for tasks required before termination of the procedure. |
Of the native functions, only the CmTransform function is required; others are optional. The CmTransform function in the template simply copies each source value to a target value. You can add your own functions to be called by one or more native functions or otherwise edit the template to generate destination values or reports that meet your requirements.
The CmStartTable and CmTransform functions must return one of the following codes:
Return Code | Mnemonic | Explanation |
---|---|---|
0 | PST_CM_EXIT_SUCCESS | Procedure executed successfully |
1 | PST_CM_EXIT_REJECT_ROW | Procedure rejected row |
2 | PST_CM_EXIT_ABORT_PROCESS | Procedure detected abort condition |
Available functions and statements and additional reserved words are listed in the following tables. See the Optim Basic Language Reference for syntax and other information about the statements and functions.
Abs | Access | Alias | And | Any |
App | AppActivate | Asc | Atn | As |
Base | Begin | Binary | ByVal | Call |
Case | CBool | CDate | ChDir | ChDrive |
Choose | Chr | Const | Cos | CurDir |
CDbl | CInt | CLng | CSng | CStr |
CVar | Close | CreateObject | Date | DateSerial |
DateValue | Day | Declare | Dim | Dir |
Do...Loop | DDEInitiate | DDEExecute | Double | Else |
ElseIf | End | EndIf | EOF | Eqv |
Erase | Err | Error | Exit | Exp |
Explicit | False | FileCopy | FileLen | Fix |
For | For Each...Next | For...Next | Format | FreeFile |
Function | Get | GetAttr | Get Object | Global |
GoTo | Hex | Hour | If...Then...Else...[End If] | Imp |
Input | Input # | InputBox | InStr | Int |
Integer | Is | IsArray | IsEmpty | IsNull |
IsNumeric | IsDate | IsObject | Kill | LBound |
LCase | Left | Len | Let | LOF |
Log | Long | Loop | LTrim | Line Input # |
Mid | Minute | MkDir | Mod | Month |
Name | Next | Not | Now | Oct |
On | Open | Object | Option | Optional |
Or | On Error | Option Base | Option Explicit | |
Print # | Put | Randomize | Rem | ReDim |
RmDir | Rnd | Rtrim | Right | Seek |
SendKeys | Set | SetAttr | Second | Select |
Select Case | Shell | Sin | Sqr | Stop |
Str | Sng | Single | Space | Static |
Step | Stop | Str | String | StrComp |
Sub | Tan | Time | Timer | TimeSerial |
Then | Trim | True | To | Type |
TimeValue | UBound | UCase | Until | Val |
Variant | VarType | Write # | Weekday | With |
While...Wend | Xor | Year |
Do...Loop | End | Exit For | Exit Loop |
For Each...Next | For...Next | GoTo | If...Then...Else...[End If] |
OnError | Select Case | Stop | While...Wend |
Asc | CBool | CDate | CDbl |
Chr | CInt | Clng | CSngr |
CStr | CVar | Date | DateSerial |
DateValue | Day | Fix | Format |
Hex | Hour | Int | Minute |
Month | Oct | Second | Str |
TimeSerial | TimeValue | Val | Weekday |
Year |
ChDir | ChDrive | Close | CurDir |
Dir | EOF | FileCopy | FileLen |
FreeFile | Get | GetAttr | Input |
Kill | Line Input | LOF | MkDir |
Name | Open | Print # | Put |
RmDir | Seek | SetAttr | Write # |
Asc | Chr | InStr | LCase |
Left | Len | Let | LTrim |
Mid | Option Compare | Right | RTrim |
Space | StrComp Format | String | Trim |
UCase |
Const | Dim | Global | IsDate |
IsEmpty | IsNull | IsNumeric | Option Explicit |
Static | VarType |
Arg | ArgCount | CompanyName | ComputerName |
DataDir | Environ | Error | Instance |
LogEvent | Opsys | OpsysBuild | OpsysCSD |
OpsysRelease | Request | RequestList | RtBuild |
RtRelease | Script | ServerUserId | StartLogging |
TempDir | ThreadHandle | ThreadId |
Function | Syntax |
---|---|
GetAsBinary | GetAsBinary(column,length) |
GetAsDate | GetAsDate(column,pointer) |
GetAsDecimalChar | GetAsDecimalChar(column,precision[,scale]) |
GetAsDecimalCharSz | GetAsDecimalCharSz(column,precision,scale) |
GetAsOracleDate | GetAsOracleDate(column,pointer) |
GetAsSybaseDateTime | GetAsSybaseDateTime(column,pointer) |
GetAsSybaseDecimal | GetAsSybaseDecimal(column,pointer) |
GetAsSybaseMoney | GetAsSybaseMoney(column,pointer) |
GetAsSybaseSmallDateTime | GetAsSybaseSmallDateTime(column,pointer) |
GetAsSybaseSmallMoney | GetAsSybaseSmallMoney(column,pointer) |
GetAsTime | GetAsTime(column,pointer) |
GetAsVarBinary | GetAsVarBinary(column,pointer) |
GetAsVarChar | GetAsVarChar(column,pointer) |
GetAsVarCharSz | GetAsVarCharSz(column,pointer) |
GetGlobalWork | GetGlobalWork(area,length) |
PutAsBinary | PutAsBinary(column,length) |
PutAsDate | PutAsDate(column,pointer) |
PutAsDecimalChar | PutAsDecimalChar(pointer) |
PutAsDecimalCharSz | PutAsDecimalCharSz(pointer) |
PutAsOracleDate | PutAsOracleDate(column,pointer) |
PutAsSybaseDateTime | PutAsSybaseDateTime(column,pointer) |
PutAsSybaseDecimal | PutAsSybaseDecimal(column,pointer) |
PutAsSybaseMoney | PutAsSybaseMoney(column,pointer) |
PutAsSybaseSmallDateTime | PutAsSybaseSmallDateTime(column,pointer) |
PutAsSybaseSmallMoney | PutAsSybaseSmallMoney(column,pointer) |
PutAsTime | PutAsTime(column,pointer) |
PutAsVarBinary | PutAsVarBinary(column,pointer) |
PutAsVarChar | PutAsVarChar(column,pointer) |
PutAsVarCharSz | PutAsVarCharSz(column,pointer) |
SetGlobalWork | SetGlobalWork(pointer,length) |
Variable | Type Specifier | Usage | Size |
---|---|---|---|
String | $ | Dim Str_Var As String | 0 to 16,000 char |
Integer | % | Dim Int_Var As Integer | 2 bytes |
Long | & | Dim Long_Var As Long | 4 bytes |
Single | ! | Dim Sing_Var As Single | 4 bytes |
Double | # | Dim Sbl_Var As Double | 8 bytes |
Variant | Dim X As Any | 4 bytes | |
Boolean | Dim X As Boolean | True or False | |
Byte | Dim X As Byte | 0 to 225 | |
Object | Dim X As Object | 4 bytes | |
Date | Dim D As Date | 8 bytes | |
Currency | Dim Cvar As Currency | 8 bytes | |
User Defined Types | size of each element |
Arithmetic operators follow mathematical rules of precedence. ‘+' or ‘&' can be used for string concatenation.
Operator | Function | Usage |
---|---|---|
^ | exponentiation | x = y^2 |
- | negation | x = -2 |
* | multiplication | x% = 2 * 3 |
/ | division | x = 10/2 |
Mod | modulo | x = y Mod z |
+ | addition | x = 2 + 3 |
- | subtraction | x = 6 - 4 |
Operator | Function | Usage |
---|---|---|
< | less than | x < Y |
<= | less than or equal to | x <= Y |
= | equals | x = Y |
>= | greater than or equal to | x >= Y |
> | greater than | x > Y |
<> | not equal to | x <> Y |
Operator | Function | Usage |
---|---|---|
Not | Logical Negation | If Not (x) |
And | Logical And | If (x>y) And (x<Z) |
Or | Logical Or | If (x=y) Or (x=z) |
Operator (in order from highest to lowest) | Description |
---|---|
() | parenthesis |
^ | exponentiation |
- | unary minus |
/,* | division/multiplication |
mod | modulo |
+, -, & | addition, subtraction, concatenation |
=, <>, <, >,<=,>= | relational |
not | logical negation |
and | logical conjunction |
or | logical disjunction |
Xor | logical exclusion |
Eqv | logical equivalence |
Imp | logical implication |
In addition to the standard commands, you can select the following commands from the File menu:
In addition to the standard commands, you can select the following command from the Edit menu: