Hi,
I have a issue on using NUM() function. Below is the details.
My source is a sequential file(.txt) and having a column (CLNT_NO) which is defined as CHAR(19) in source file.
In a stage variable I am checking If it's numeric or not.
If Len(Trim(CLNT_NO))=0 Then Reject REC Else (
If Num(CLNT_NO) Then Pass REC Else Rej REC)
But issue is now the Num is not working even if I have valid numeric value from source. The record is rejecting with reason CLNT_NO is not numeric.
Please suggest what could have reason for this.
Thanks
NUM function
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Since it's char(19) and you have numbers stored in the column, you may have spaces padded after the number.
Spaces, or whatever your PADCHAR is, are not valid numeric values. Either change the datatype to varchar or trim the spaces before performing the numeric check.
Code: Select all
For e.g. '123 '
Spaces, or whatever your PADCHAR is, are not valid numeric values. Either change the datatype to varchar or trim the spaces before performing the numeric check.
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
-
- Participant
- Posts: 14
- Joined: Wed Feb 01, 2012 5:26 am
- Location: Hyd
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
I don't see the trim applied to the NUM function's parameter in the code you've posted. Kindly post the updated code.
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
-
- Premium Member
- Posts: 730
- Joined: Tue Nov 04, 2008 10:14 am
- Location: Bangalore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If there's really a leading single quote character (as per your example, possibly grabbed from Excel), then the Num() function will identify this as non-numeric. You need to lose the leading quote character first.
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.