NUM function

dslearner07 · Post by **dslearner07** » Tue Nov 20, 2012 1:07 am

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

jerome_rajan · Post by **jerome_rajan** » Tue Nov 20, 2012 1:29 am

Since it's char(19) and you have numbers stored in the column, you may have spaces padded after the number.

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.

dslearner07 · Post by **dslearner07** » Tue Nov 20, 2012 2:22 am

I have used Trim before doing num function. Still facing same issue.

jerome_rajan · Post by **jerome_rajan** » Tue Nov 20, 2012 2:26 am

I don't see the trim applied to the NUM function's parameter in the code you've posted. Kindly post the updated code.

zulfi123786 · Post by **zulfi123786** » Tue Nov 20, 2012 5:32 am

Also make sure there are no period ('.') character in the trailing part apart from the spaces. Also check for the existence of non printable characters by finding the length of the string.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 20, 2012 1:26 pm

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.