Decimals in RCP
Moderators: chulett, rschirm, roy
Decimals in RCP
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Similarly, to specify a data type in DataStage one must name the column. Therefore this approach will not work.ssnegi wrote:Read the as decimal column in database as varchar in datastage. This way there wont be any unwanted zeros.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
There is a solution in the SELECT statement.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Or you can use CAST if your database doesn't support the syntax that elsont gave.
If that has leading zeroes, space or trailing "." you can remove those with TRIM() functions in the SQL.
Code: Select all
SELECT CAST(PAY_AMT AS VARCHAR(9)) AS PAY_AMT_STRING FROM tablename;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:(...)"
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:(...)"
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: