I am using Teradata SQL Assistant v14.10 and want to manipulate the way result is displayed when I run my query. I want to do the following things-
#1: Tools - Options - Export - Uncheck "Write column headers to the export file"
#2: use TO_CHAR to change the format to 'HH24MI'
#3: ??? This question doesn't parse :-)
#4: TO_CHAR(x, 'DAY)
I also want to use LPAD function. But, when I write the query, it says invalid expression. I want to do something like this--
ABC 3234 YYY
AB 222 ZZZ
QQQ 3333 WWW
So, essentially all these 3 columns are concatenated together. And, I want the ability to align a column to left or right and also to make sure that each column starts from the same position.
Whithout the actual statement it's hard to say why it fails, you said you're n TD14.10, so LPAD exists.
I can briefly show you what the statement looks like--
LPAD(COL1, 4, ' ' ) || LPAD (COL2, 5, ' ' ) AS COL3
With this statement now I am getting error that says "A column or character expression is larger than the max size"
LPAD returns 32000 or 64000 chars, you need to cast to a smaller size:
CAST(LPAD(COL1, 4) AS CHAR(4)) || CAST(LPAD (COL2, 5) ASCHAR(5)) AS COL3
Btw, no need for ' ', this is the default pad character.
All right. This worked. How do I however do it for numeric values? For something like "27" which is in COL4 and "abc22" which is in COL5. I tried the following statements--
CAST (RPAD(COL4, 2) as CHAR(2) )
CAST (RPAD(COL4, 2) as INT(2) )
CAST (RPAD(COL4, 2) as NUMBER(2) )
CAST (RPAD(COL5, 5) as VARCHAR(2) )
None of this worked!
LPAD/RPAD only work on strings, so non-character data must be CASTed first.
And your CASTs try to convert the result of the xPAD back to the original datatype?
But do you actually need xPAD at all?
When you CAST(col AS CHAR(n)) it's the same as RPAD(col, n).
If you want numeric columns to be left padded you might simply use old Teradata casts like col (char(10)).
However most users prefer delimited data, why do you need fixed size?
It is needed to enter into another software.
Thank you so much. I think that helps . And, answers my questions.