In this example, the items in the first virtual input column have trailing special characters. No delimiter separates the virtual columns. ColumnLength is 2. The call to Unpack includes IgnoreInvalid ('true'), but the output is unexpected.
Input
| c1 |
|---|
| 1,1919-04-05 |
| 1.1919-04-05 |
| 5,.1919-04-05 |
| 2,2019/04/05 |
| 4.,.1919-04-05 |
| 32019/04/05 |
SQL Call
SEL * FROM Unpack (
ON t2
USING
TargetColumn ('c1')
OutputColumns ('a','b')
OutputDataTypes ('int','date')
ColumnLength ('2','*')
IgnoreInvalid ('True')
) AS dt;
Output
| a | b |
|---|---|
| 1 | 19/04/05 |
| 1 | 19/04/05 |
| 2 | 19/04/05 |
The reason for the unexpected output is the behavior of an internal library that Unpack uses, which is as follows:
| Input Row | Behavior |
|---|---|
| 1,1919-04-05 | Library prunes trailing comma, converts "1" to integer and "1919-04-05" to date. (Output row 1.) |
| 1.1919-04-05 | Library prunes trailing period, converts "1" to integer and "1919-04-05" to date. (Output row 2.) |
| 5,.1919-04-05 | Library prunes trailing comma, converts 5 to integer, but cannot convert ".1919-04-05" to date. (No output row.) With ColumnLength ('3','*'), library prunes trailing comma and period, converts "5" to integer and "1919-04-05" to date, and outputs a row for this input row. |
| 2,2019/04/05 | Library prunes trailing comma, converts "2" to integer and "1919/04/05" to date. (Output row 3.) |
| 4.,.1919-04-05 | Library converts "4." to integer, but cannot convert ",.1919-04-05" to date. (No output row.) |
| 32019/04/05 | Library converts "32" to integer, but cannot convert "019/04/05" to date. (No output row.) |