Decimals in RCP

elsont · Post by **elsont** » Mon May 12, 2014 9:54 am

Hi,

I have a generic RCP enabled job (Teradata Connector -> Copy Stage -> Sequential file stage) to extract data from Teradata into a text file. It accepts SELECT query on run time and creates the file. The issue I am facing is with decimal columns were DataStage adds zeros before and after. I would like to have this job creates file without these unwanted zeros (like applying DecimalToString(%number%, "suppress_zero") to all decimals).
Please let me know if there are any ways to achieve this other than converting all decimals to strings in the select query.

Thanks,
Elson

ray.wurlod · Post by **ray.wurlod** » Mon May 12, 2014 3:12 pm

No.

If you are going to transform the data in a column you must name that column.

ssnegi · Post by **ssnegi** » Mon May 12, 2014 8:50 pm

Read the decimal column in database as varchar in datastage. This way there wont be any unwanted zeros.

ray.wurlod · Post by **ray.wurlod** » Mon May 12, 2014 11:43 pm

ssnegi wrote:Read the as decimal column in database as varchar in datastage. This way there wont be any unwanted zeros.

Similarly, to specify a data type in DataStage one must name the column. Therefore this approach will not work.
I suspect what was actually being suggested is CAST columnname AS VARCHAR(size) in the extraction SQL, but this was explicitly excluded in the original question.

elsont · Post by **elsont** » Wed May 14, 2014 11:23 am

Thanks for Reply. I don't want to do any transformations. Just need to write the data from Teradata view/s into the sequential file. We have many fields with data type decimal (18, 0) and decimal (11, 2). File becomes big because of these extra zeros. Also no one likes to see lot of preceding zeros before a small number.
I am trying to create a generic extract which can accept a select query and write output into a delimited text file. Now I see only two options, 1) Convert decimals to string in the select query (who ever use this job needs to remember about this limitation and make sure all decimals are converted). 2) have a post processing code to remove preceding zeros from decimals.

ray.wurlod · Post by **ray.wurlod** » Wed May 14, 2014 3:24 pm

I know what you're trying to do, and there's no solution in DataStage other than naming the columns from which you want to strip the zeroes.

There is a solution in the SELECT statement.

vamsi.4a6 · Post by **vamsi.4a6** » Wed May 14, 2014 11:00 pm

@elsont

could you please elaborate on this point.how to write this query in selection criteria?

Convert decimals to string in the select query (who ever use this job needs to remember about this limitation and make sure all decimals are converted)

I am new to RCP any input is really appreciated.

elsont · Post by **elsont** » Thu May 15, 2014 2:38 pm

Hi,
See below two queries. PAY_AMT is decimal and PAY_AMT_STRING is string.
1) SEL PAY_AMT FROM TABLE
2) SEL PAY_AMT(FORMAT 'Z9') (VARCHAR(9)) AS PAY_AMT_STRING FROM TABLE

ray.wurlod · Post by **ray.wurlod** » Thu May 15, 2014 2:49 pm

Or you can use CAST if your database doesn't support the syntax that elsont gave.

Code: Select all

SELECT CAST(PAY_AMT AS VARCHAR(9)) AS PAY_AMT_STRING FROM tablename;

If that has leading zeroes, space or trailing "." you can remove those with TRIM() functions in the SQL.

elsont · Post by **elsont** » Thu May 15, 2014 3:01 pm

OK, I think IBM should keep an option in sequential file stage to write decimals without these extra zeros. I have put a workaround using a routine to pull the metadata from the log and a job with loop in transformer to remove the unwanted zeros.

ray.wurlod · Post by **ray.wurlod** » Thu May 15, 2014 5:08 pm

Are you able to share this routine? From which log are you pulling the metadata?

elsont · Post by **elsont** » Fri May 16, 2014 8:50 am

Below function will give Log Summary
DSGetLogSummary(JobHandle,DSJ.LOGINFO ,StartDate,EndDate, 0)Loop through the log and get Event ID (EventId) of line starts with "main_program: Schemas:"
Use below function to get all the schemas used in the job.
LogSchemas = DSGetLogEntry (JobHandle, EventId)There will be three schemas and all will be same (Teradata stage, copy stage and seq file stage).

Only concern is I am looking for string "main_program: Schemas:" in the job log to get Event Id which has schemas. I am not sure if it is same in Versions 8.7 and 9.1 (I am using 8.5). It will be great if somebody confirm if it is same in 8.7 and 9.1. You can see it in the director log as Type -> Info, Event -> "main_program: Schemas:(...)"

ray.wurlod · Post by **ray.wurlod** » Fri May 16, 2014 3:15 pm

OK, so you also use the environment variable to have DataStage write the schemas into the log file. This is a minor, but important, part of your solution.