Home > Data > Database Linkage > Data Field Links > SQL Override Statement

SQL Override Statement

SQL Override Statement is a free form box for entering SQL code. There are a few rules that must be followed when using SQL Override Statement:

  1. The SQL statement must return 2-4 items in this required order:

a. Person ID - for scalar and array fields

b. Value for the field - for scalar and array fields

c. Effective/Start date - for any array field

d. Stop date - for start/stop array fields

  1. $PID must be used as a required placeholder for the Person ID. At execution, it is replaced with the actual Person ID of the person you are processing. When using character Person IDs $PID should be enclosed in single or double quotes (e.g., '$PID' or "$PID"). 

  2. If you are using the Include global WHERE, then you will have to manually add the global WHERE to your SQL override. 

Note: For scalar fields, if this SQL statement returns more than one row, the value in the first row will be used.

Example: Suppose the client has a frozen benefit whose effective date is the date the location decided to adopt a new formula. The database table called “Person” contains the employee social security number named “SSN” and four (4) separate numerical columns from which you want to map to an amount, FrozenBenefit and its effective date. The fields are: FrozenBen, M_FrozenDate, D_FrozenDate and Y_FrozenDate for the frozen benefit and its effective date. The SQL Override Statement could be written as:

SELECT PersonID, FrozenBen, CAST(CAST(M_ FrozenDate AS VARCHAR(2))+ CAST(D_ FrozenDate AS VARCHAR(2)) + CAST(Y_ FrozenDate AS VARCHAR(4)) AS DATETIME) FROM Person

WHERE (SSN = '$PID')

This statement provides three values:

a. the employee’s social security number to use as Person ID

b. the value for the field - FrozenBen

c. the effective date of the frozen benefit

 

Note: $PID is a required placeholder. At execution, it is replaced with the actual Person ID… reminds us how to use $PID.

Validate button when clicked will test the SQL code in the SQL Override Statement box for errors. It will check the table and field names on the database; check the field parameters for correctness of the selected items and the SQL code itself for compatibility. It will provide error messages when the SQL code does not pass the tests and will produce the message “Validation tests passed” when ProAdmin can use the SQL code as entered.