Kognitio Console issues, advice, etc.
Single Poster
User avatar
Posts: 1
Joined: Wed Aug 05, 2015 4:31 pm

TO_CHAR giving character length of 32000

by Abhisrajput » Wed Aug 05, 2015 4:37 pm


I am using TO_CHAR function and its giving me the column charcter length of 32000.

Code snippet
'Q' || TRIM(TO_CHAR(AS_OF_DT , 'fmQ')) AS Quarter,
TRIM(TO_CHAR(AS_OF_DT , 'fmMon')) AS MonthName

Please help.
Reply with quote Top
Posts: 185
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: TO_CHAR giving character length of 32000

by MikeAtkinson » Thu Aug 06, 2015 9:31 am

TO_CHAR() gives a character length of 32000 because in general it is not possible to determine the length until the query is completes execution. Consider:

Code: Select all

create table qwerty (c1 date, c2 varchar);
insert into qwerty values (current_date, 'fmQ'), (current_date, 'fmMon');
select to_char(c1, c2) from qwerty;
The solution is to use a cast, so in the above example:

Code: Select all

select cast(to_char(c1, c2) as varchar(20)) from qwerty;
Or in the case of the code snippet you posted:

Code: Select all

select cast('Q' || TRIM(TO_CHAR(AS_OF_DT , 'fmQ')) as varchar(30)) AS Quarter, 
cast(TRIM(TO_CHAR(AS_OF_DT , 'fmMon')) as varchar(40)) AS MonthName;
It would be better to use a character length of less than 32000 if it could be statically determined, as this would avoid the need for casts. I've passed this to the developers working on the SQL compiler.
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 2 guests