BTEQ not remove trailing blank

Tools
Enthusiast

BTEQ not remove trailing blank

When I run a macro in the Teradata SQL Assistant in Export Result mode, I get a file content  that looks like this:

123____2344__23455____

123____2344__23455____

Etc.

The point here is that each line must have the same length, and the “_” represent spaces.

But if I run the same macro from BTEQ:

.LOGON xxx

.Format OFF

.SET WIDTH 500

.EXPORT report file = WEEK39.txt

 EXEC POSCN_A_P.Ma_Macro(24);

.Export Reset

.LOGOFF

.EXIT

I get the following result:

123____2344__23455

123____2344__23455

Etc.

Where the trailing blanks has been removed. How do I prevent the trailing blanks from being removed when running the macro in a BTEQ?

Peter Schwennesen

Tags (1)
6 REPLIES
Senior Apprentice

Re: BTEQ not remove trailing blank

Hi Peter,

AFAIK there's no way to get trailing blanks in a BTEQ REPORT, this format was used to directly print reports and there's no reason to print trailing blanks :-)

A numeric value like the one in your example is usually right aligned, so if the last column was numeric this would avoid the truncation.

How does the Select within the macro look like? 

Dieter

Enthusiast

Re: BTEQ not remove trailing blank

Hi Dieter

The outer part of my select is:

So this means that if I format the last column like: ________123 in stead of 123______ then I get the defined record legnth? (_ is space)

br

Peter Schwennesen

SELECT SUBSTR(CAST(A.UgeId AS CHAR(6)),3,6) ||

SUBSTR(CAST(A.ButikId AS CHAR(20)),1,7) ||

CAST(SUBSTR(CAST(PluId AS CHAR(14)), 1 , INDEX(CAST(PluId AS CHAR(14)), '.') - 1) || ' ' AS CHAR (13)) ||

SUBSTR(CAST((CASE

WHEN A.OmsSalgStk < 0 THEN 10000000

ELSE A.OmsSalgStk + 10000000

END) AS CHAR(8)) ,3) ||

SUBSTR(CAST((CASE

WHEN A.OmsSalgInclMomsDKK < 0 THEN 1000000000

ELSE A.OmsSalgInclMomsDKK + 1000000000

END) AS CHAR(10)),2) ||

SUBSTR(CAST((CASE

WHEN A.AvgPris < 0 THEN 10000000

ELSE A.AvgPris + 10000000

END) AS CHAR(8)) ,2) ||

SUBSTR(CAST(A.Varetekst AS CHAR(30)),1,30) ||

SUBSTR(A.MaengdeEnhedNavn,1,3) ||

SUBSTR(CAST((CASE

WHEN A.Volumen < 0 THEN 1000000

ELSE A.Volumen + 1000000

END) AS CHAR(7)),2) ||

SUBSTR(CAST(A.BestNr AS CHAR(13)),1,13)

(TITLE '') --AS detail

FROM (

SELECT ...

) AS A

ORDER BY 1 DESC

;

Senior Apprentice

Re: BTEQ not remove trailing blank

Hi Peter,

yes, correct :-)

Just change the CAST from ANSI to Teradata syntax, ANSI casts numeric values left aligned whereas Teradata does it right aligned:

A.BestNr (CHAR(13))

Caution, if the maximum number of characters (based on the column's FORMAT) is greater than 13 this cast will strip some digits.

Assuming BestNr is a numeric column you might simply add a FORMAT:

A.BestNr (CHAR(13), FORMAT 'Z(13)')

Btw, you don't have to use a SubString and Cast unless you want to extract a specific portion of the string.

And you don't have to concat all the columns, you probably do it because you don't want do all those (TITLE ''):

Change EXPORT REPORT to 

EXPORT DATA followed by SET RECORDMODE OFF

This omits the title plus the titledashes (undocumented but quite nice)

If you don't want blanks between columns you can SET SEPARATOR '' or SET SEPARATOR 0

And finally: instead of typecasing to char/substring you might better add FORMATs, thus you got better control over the formatting (and REPORT format automatically applies the cast to char)

Dieter
Enthusiast

Re: BTEQ not remove trailing blank

Hi Dieter

Thanks for the information.

Basically this SQL is some code left over from a college of mine, and not to introduce all too many "errors" I have kept the substring and cast() as he wrote it.

But your input has been of great help and inspiration.

br

Peter Schwennesen

Enthusiast

Re: BTEQ not remove trailing blank

Hi,

Dnoeth

While i'm trying to load data from staging to Edw using Teradata macros and  Informatica ,how i can Excute macro in informatica .

could you please share any solution for this 

Thanks & Regards

Enthusiast

Re: BTEQ not remove trailing blank

You can create a new task in informatica and in the properties you can add - exec macroname .. and run that task using informatica (assuming you have all setup connection already existing between informatica and teradata)