Failure 5628 Column SYSLIB not found in Table

Database
Enthusiast

Failure 5628 Column SYSLIB not found in Table

Hi,

 

I am facing error while replacing LENGTH to CHARACTER_LENGTH(trim(trailing from COLUMN)) for below view definition

 

REPLACE VIEW  viewname AS LOCKING ROW ACCESS
(SELECT col1,col2,col3,col4,col5
UNION
SELECT col1,

col2,
SUBSTR((SUBSTR('0000000000',1 ,10 - ("SYSLIB"."length"
( CAST((col) AS VARCHAR(10)
CHARACTER SET LATIN NOT CASESPECIFIC)(VARCHAR(64000), CHARACTER SET LATIN,
NOT CASESPECIFIC)))))))||(CAST((col) AS VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)),5 ,4 )(NAM
ED "X" ),
SUBSTR((SUBSTR('0000000000',1 ,10 - ("SYSLIB"."length"
( CAST((col) AS VARCHAR(10)
CHARACTER SET LATIN NOT CASESPECIFIC)(VARCHAR(64000), CHARACTER SET LATIN,
NOT CASESPECIFIC)))))))||(CAST((col ) AS VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)),9 ,2 )(NAM
ED "Y" ),

col4,

col5
FROM table);

 

Error:Failure  5628 Column SYSLIB  not found in table

 

Please help me with the solution and do the needful.

 

Thanks,

Pavan


Accepted Solutions
Teradata Employee

Re: Failure 5628 Column SYSLIB not found in Table

Yes, because you still didn't remove the  "SYSLIB".  (two places).

1 ACCEPTED SOLUTION
5 REPLIES 5
Ambassador

Re: Failure 5628 Column SYSLIB not found in Table

There's no function named LENGTH in SYSLIB.

 

You probably want to split a numeric column into two strings with leading zeroes using a way too complicated calculation, there ware several simpler ways, e.g.

 Substr(Trim(col (FORMAT '9(10)')), 5,4)
Teradata Employee

Re: Failure 5628 Column SYSLIB not found in Table

I agree that you'd probably be better off simplifying.

But to respond to your question directly:

It seems your site has an old "LENGTH" user-defined function installed in "SYSLIB" that implicitly trims data. In that case you could replace

"SYSLIB"."length"( rest_of_expression )

with

CHARACTER_LENGTH(TRIM(TRAILING FROM rest_of_expression ))

Enthusiast

Re: Failure 5628 Column SYSLIB not found in Table

Hi Fred,

 

When i tried below SQL TD throws  *** Failure 3707 Syntax error, expected something like an 'UDFCALLNAME' key
word between '.' and the 'CHARACTER_LENGTH' keyword.
Statement# 1, Info =2260

 

SQL:

 

REPLACE VIEW  viewname AS LOCKING ROW ACCESS
(SELECT col1,col2,col3,col4,col5
UNION
SELECT col1,

col2,
SUBSTR((SUBSTR('0000000000',1 ,10 - ("SYSLIB".CHARACTER_LENGTH(trim(trailing from
( CAST((col) AS VARCHAR(10)
CHARACTER SET LATIN NOT CASESPECIFIC)(VARCHAR(64000), CHARACTER SET LATIN,
NOT CASESPECIFIC)))))))||(CAST((col) AS VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)),5 ,4 )(NAM
ED "X" ),
SUBSTR((SUBSTR('0000000000',1 ,10 - ("SYSLIB".CHARACTER_LENGTH(trim(trailing from
( CAST((col) AS VARCHAR(10)
CHARACTER SET LATIN NOT CASESPECIFIC)(VARCHAR(64000), CHARACTER SET LATIN,
NOT CASESPECIFIC)))))))||(CAST((col ) AS VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)),9 ,2 )(NAM
ED "Y" ),

col4,

col5
FROM table);

 

Thanks,

Pavan

Teradata Employee

Re: Failure 5628 Column SYSLIB not found in Table

Yes, because you still didn't remove the  "SYSLIB".  (two places).

Teradata Employee

Re: Failure 5628 Column SYSLIB not found in Table

Hi Pavan,

 

The formula in itself seems way too complicated.

Can you sample some input values for the column col, it's datatype and what you expect as an output ?

 

If col is an integer, I feel this formula should give the same results :

     , substring(to_char(col, 'fm0000000000') from 5 for 4) as "X"    
     , substring(to_char(col, 'fm0000000000') from 9 for 2) as "Y"