Unpack Function Example | Teradata Vantage - Example: Input Columns with Trailing Special Characters - Analytics Database

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantageā„¢

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

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

Unpack SQL Call with IgnoreInvalid ('true') with Trailing Special Characters

SELECT * FROM Unpack (
  ON t2 AS InputTable
  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.