All Forums General
anusuit1190 13 posts Joined 05/15
15 Jun 2015
Changing display/format of Teradata SQL Assistant Result

Hi,
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-

  • When exporting result in TXT file, I do not want to display the column names.
  • I want the time format in “hhmm” instead of “hh:mm”

 

  • Concatenate two columns (COL_1 and COL_2). COL_1 is text and COL_2 is number. After concatenating I want to align it to right and left in 2 separate columns. More importantly, I want to do something like this COL_1SpaceSpaceCOL_2SpaceSpaceSpaceSpaceCOL_3.
  • Displaying day of week. As in, based on date how can I display whether it was Monday or Tuesday? I have 1 year worth of data.
dnoeth 4628 posts Joined 11/04
15 Jun 2015

#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)

Dieter

anusuit1190 13 posts Joined 05/15
16 Jun 2015

Thanks. This helps.

anusuit1190 13 posts Joined 05/15
16 Jul 2015

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.

dnoeth 4628 posts Joined 11/04
16 Jul 2015

Whithout the actual statement it's hard to say why it fails, you said you're n TD14.10, so LPAD exists.

Dieter

anusuit1190 13 posts Joined 05/15
16 Jul 2015

I can briefly show you what the statement looks like--
SELECT
LPAD(COL1, 4, ' ' ) || LPAD (COL2, 5, ' ' ) AS COL3
FROM
( ......
.....)
With this statement now I am getting error that says "A column or character expression is larger than the max size"
 

dnoeth 4628 posts Joined 11/04
16 Jul 2015

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.

Dieter

anusuit1190 13 posts Joined 05/15
16 Jul 2015

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!

dnoeth 4628 posts Joined 11/04
16 Jul 2015

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?

Dieter

anusuit1190 13 posts Joined 05/15
16 Jul 2015

It is needed to enter into another software.
Thank you so much. I think that helps . And, answers my questions.

You must sign in to leave a comment.