When I run a macro in the Teradata SQL Assistant in Export Result mode, I get a file content that looks like this:
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:
.SET WIDTH 500
.EXPORT report file = WEEK39.txt
I get the following result:
Where the trailing blanks has been removed. How do I prevent the trailing blanks from being removed when running the macro in a BTEQ?
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?
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)
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)) ||
WHEN A.OmsSalgStk < 0 THEN 10000000
ELSE A.OmsSalgStk + 10000000
END) AS CHAR(8)) ,3) ||
WHEN A.OmsSalgInclMomsDKK < 0 THEN 1000000000
ELSE A.OmsSalgInclMomsDKK + 1000000000
END) AS CHAR(10)),2) ||
WHEN A.AvgPris < 0 THEN 10000000
ELSE A.AvgPris + 10000000
END) AS CHAR(8)) ,2) ||
SUBSTR(CAST(A.Varetekst AS CHAR(30)),1,30) ||
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
) AS A
ORDER BY 1 DESC
yes, correct :-)
Just change the CAST from ANSI to Teradata syntax, ANSI casts numeric values left aligned whereas Teradata does it right aligned:
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 ''):
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.
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
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)