teradata column type in a view object

Database
Highlighted
Enthusiast

teradata column type in a view object

Hi,

 

I am working on TERADATA  15..

 

I have a view and I want to know each column type of this view

It seems that is possible starting from teradata 16.0 so I opted for the type function like so

 

select
type (v.column_filter) as key_filter_type,
v.*
from db_name.view_name v
where
(

-- if my column_filter is of type CHAR
(key_filter_type <> 'date'  and v.column_filter = '201711')
or

-- if my column_filter is of type DATE or TIMESTAMP
(key_filter_type='date'  and ( v.column_filter (FORMAT 'YYYYMM')(CHAR(6)) = '201711' ) )
)

 

I get this error

 

teradata format string 'YYYYMM' has combination of numeric character and graphic values

 

My  query is generic  so my column column_filter can be either of type CHAR or DATE (or TIMESTAMP) (depending on the view passed in parameter for the query)

 

So, how can I write my where clause  without this error ?

 

Any help ? thanks

 

 


Accepted Solutions
Teradata Employee

Re: teradata column type in a view object

Oh, that's right. The 3527 error occurs during parsing / optimization not during execution.

 

It's probably easiest to determine the data types first and decide which query or queries to generate.

 

The only way I can think of to address different data types at optimization time is to define overloaded functions with different implementation depending on the input argument types.  For example:

 

CREATE FUNCTION mydb.GetKeyFilter (a DATE)
RETURNS VARCHAR(255)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SPECIFIC mydb.GetKeyFilterDate
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN a (FORMAT 'YYYYMM') (CHAR(6));

CREATE FUNCTION mydb.GetKeyFilter (a VARCHAR(255))
RETURNS VARCHAR(255)
LANGUAGE SQL DETERMINISTIC CONTAINS SQL SPECIFIC mydb.GetKeyFilterChar RETURNS NULL ON NULL INPUT SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN a;

And your WHERE clause becomes

 

WHERE mydb.GetKeyFilter(v.column_filter) = '201711'

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: teradata column type in a view object

How about a CASE expression? For example,

 

CASE WHEN key_filter_type <> 'date'  THEN v.column_filter

ELSE v.column_filter (FORMAT 'YYYYMM')(CHAR(6)) END = '201711'

Enthusiast

Re: teradata column type in a view object

Hi

I still have the same error after I have modified the query.

 

select
type (v.column_filter) as key_filter_type,
v.*
from db_name.view_name v
where
CASE WHEN key_filter_type <> 'date'  THEN v.column_filter
ELSE (v.column_filter (FORMAT 'YYYYMM')(CHAR(6)) )END = '201711'

 

The problem is that, even if key_filter_type is of type CHAR (not date) the else clause is still evaluated

==> this is why I am getting this error !!

format string 'YYYYMM' has combination of numeric character and graphic values

 

Any help ?

 

Thaks

 

Teradata Employee

Re: teradata column type in a view object

Oh, that's right. The 3527 error occurs during parsing / optimization not during execution.

 

It's probably easiest to determine the data types first and decide which query or queries to generate.

 

The only way I can think of to address different data types at optimization time is to define overloaded functions with different implementation depending on the input argument types.  For example:

 

CREATE FUNCTION mydb.GetKeyFilter (a DATE)
RETURNS VARCHAR(255)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SPECIFIC mydb.GetKeyFilterDate
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN a (FORMAT 'YYYYMM') (CHAR(6));

CREATE FUNCTION mydb.GetKeyFilter (a VARCHAR(255))
RETURNS VARCHAR(255)
LANGUAGE SQL DETERMINISTIC CONTAINS SQL SPECIFIC mydb.GetKeyFilterChar RETURNS NULL ON NULL INPUT SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN a;

And your WHERE clause becomes

 

WHERE mydb.GetKeyFilter(v.column_filter) = '201711'

Enthusiast

Re: teradata column type in a view object

it's a good idea

 

I didn't know teradata sql is object oriented :)

 

works fine

 

Thanks a lot Fred