All Forums Tools
14 Mar 2013
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:
dnoeth 4628 posts Joined 11/04
14 Mar 2013

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

Dieter

14 Mar 2013

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
;

dnoeth 4628 posts Joined 11/04
14 Mar 2013

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
 

Dieter

14 Mar 2013

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

skrafi 6 posts Joined 10/11
16 Mar 2013

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

skmdrf

KS42982 137 posts Joined 12/12
18 Mar 2013

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)

You must sign in to leave a comment.