hello everyone,
i have a decimal field and i need to make sure that it does not contain any spaces anywhere. i tried to directly use trim function on this field but as i know its giving compiling problems.so in the transformer i have taken a stage variable
sv=col1
and this sv is of course varchar and then i used the trim function
trim(sv," ","A"). this is working and removing the spaces anywhere they are but i have to check the nullability also. i mean i have to use if condition, something like this:
If IsNull(trim(sv," ","A")) then "null" else "abc".
when i try to compile this it throws errors. Does IsNull function not work on a trimmed field??
the output field would be varchar.
please someone help.
trimming of decimals
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 53
- Joined: Fri Mar 07, 2008 1:17 am
trimming of decimals
ETL DEVELOPER
What error does it "throw" at compile time?
Also, check for NULL before doing any computations on the field.
Also, check for NULL before doing any computations on the field.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
Decimal fields can't contain spaces - so I'm assuming you're talking about a string field containing string representations of decimal values?
I think your logic would be better expressed as:
... as trimming a nullable string to empty does not make it null.
J.
PS. I hope 'sv' isn't your REAL stage variable name!
I think your logic would be better expressed as:
Code: Select all
if trim(sv," ","A") = "" then "null" else "abc"
J.
PS. I hope 'sv' isn't your REAL stage variable name!
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Trim function doesn't work in conjuction with IsNull.
Try StripWhiteSpace(%string%), if you want to just strip off spaces in the string.
Try StripWhiteSpace(%string%), if you want to just strip off spaces in the string.
Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives.
By William A.Foster
By William A.Foster
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)