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.
Unpack 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 |
Unpack SQL Call with IgnoreInvalid ('true') with Trailing Special Characters
SEL * FROM Unpack (
ON t2
USING
TargetColumn ('c1')
OutputColumns ('a','b')
OutputDataTypes ('int','date')
ColumnLength ('2','*')
IgnoreInvalid ('True')
) AS dt;
Unpack Output with IgnoreInvalid ('true') with Trailing Special Characters
| 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. |