Changing display/format of Teradata SQL Assistant Result

General
Enthusiast

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.
9 REPLIES
Junior Contributor

Re: Changing display/format of Teradata SQL Assistant Result

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

Enthusiast

Re: Changing display/format of Teradata SQL Assistant Result

Thanks. This helps.

Enthusiast

Re: Changing display/format of Teradata SQL Assistant Result

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.

Junior Contributor

Re: Changing display/format of Teradata SQL Assistant Result

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

Enthusiast

Re: Changing display/format of Teradata SQL Assistant Result

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"

Junior Contributor

Re: Changing display/format of Teradata SQL Assistant Result

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.

Enthusiast

Re: Changing display/format of Teradata SQL Assistant Result

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!

Junior Contributor

Re: Changing display/format of Teradata SQL Assistant Result

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?

Enthusiast

Re: Changing display/format of Teradata SQL Assistant Result

It is needed to enter into another software.

Thank you so much. I think that helps . And, answers my questions.