Unpack Function Example | Teradata Vantage - 17.05 - Unpack Example: IgnoreInvalid ('true') with Trailing Special Characters - Teradata Database

Teradata Vantage™ - Advanced SQL Engine Analytic Functions

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1206-170K

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

t2
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.)