Skip to content

Latest commit

 

History

History
 
 

teradata

Teradata UDFs

This directory contains user-defined functions which mimic the behavior of proprietary functions in Teradata. Each UDF within this directory will be automatically synchronized to the bqutil project within the td dataset for reference in queries.

For example, if you'd like to reference the nullifzero function within your query, you can reference it like the following:

SELECT bqutil.td.nullifzero(0)

UDFs

Documentation

Decode function

Decode function compares expression expr with search parameters (s1,s2,...,sN) and returns n-th match from result parameters (r1,r2,...,rN). Decode supports up to 10 search parameters.

More details can be found in Teradata docs.

To match this functionality in BigQuery, we can define a UDF for each number of search parameters. Note the def can be set to NULL but the type must match the type of the result parameters. If NULL is passed, it should be casted to the proper type.

Returns r1 if the expr is equal to s1, else def is returned.

SELECT bqutil.td.decode1(1, 1, 'One', CAST(NULL as STRING))
  , bqutil.td.decode1(0, 1, 'One', CAST(NULL as STRING))
  , bqutil.td.decode1('True', 'True', 1, 0)
  , bqutil.td.decode1('False', 'True', 1, 0)
  , bqutil.td.decode1(1, 1, 'One', 'Not One')
  , bqutil.td.decode1(0, 1, 'One', 'Not One')


'One', null, 1, 0, 'One', 'Not One'

Returns r1 if the expr is equal to s1, r2 if the expr is equal to s2, else def is returned.

SELECT bqutil.td.decode2(1, 1, 'True', 0, 'False', '')
  , bqutil.td.decode2(0, 1, 'True', 0, 'False', 'def')
  , bqutil.td.decode2(3, 1, 'True', 0, 'False', CAST(NULL as STRING))

'True', 'False', null

Returns r1 if the expr is equal to s1, r2 if the expr is equal to s2, r3 if the expr is equal to s3, else def is returned.

SELECT bqutil.td.decode3(1, 1, 'True', 0, 'False', NULL, 'False', 'Invalid')
  , bqutil.td.decode3(0, 1, 'True', 0, 'False', NULL, 'False', 'Invalid')
  , bqutil.td.decode3(100, 1, 'True', 0, 'False', NULL, 'False', 'Invalid')
  , bqutil.td.decode3('F', 'F', 'Female', 'M', 'Male', 'O', 'Other', CAST(NULL as STRING))
  , bqutil.td.decode3('True', 'True', True, 'False', False, '', False, CAST(NULL as BOOLEAN))

'True', 'False', 'Invalid', 'Female' ,true

Returns the 1-based index of the first occurrence of string_expr2 inside string_expr1. Teradata docs

SELECT bqutil.td.index('BigQuery', 'Query')

4

Returns the number of months between date_expr1 and date_expr2. Teradata docs

SELECT bqutil.td.months_between('2019-01-01', '2019-07-31')
  , bqutil.td.months_between('2019-07-31', '2019-01-01')

-6, 6

Returns NUll if the expr evaluates to 0. Teradata docs

SELECT bqutil.td.nullifzero(NULL)
  , bqutil.td.nullifzero(0)
  , bqutil.td.nullifzero(1)

NULL, NULL, 1

Returns expr2 if expr1 evaluates to NULL, else expr1. Teradata docs

SELECT bqutil.td.nvl(NULL, 2.0)
  , bqutil.td.nvl(1.0, 2.0)

2.0, 1.0

Returns expr3 if expr1 evaluates to NULL, else expr2. Teradata docs

SELECT bqutil.td.nvl2(NULL, 2.0, 3.0)
  , bqutil.td.nvl2(1.0, 2.0, 3.0)

3.0, 2.0

Returns source_string with every occurrence of each character in from_string replaced with the corresponding character in to_string. Teradata docs

SELECT bqutil.td.otranslate('Thin and Thick', 'Thk', 'Sp')

'Spin and Spic'

Returns 0 if the expr evaluates to NULL. Teradata docs

SELECT bqutil.td.zeroifnull(NULL)
  , bqutil.td.zeroifnull(0)
  , bqutil.td.zeroifnull(1)

0, 0, 1