Problem with NUM function

[swathi Singamareddygari]

Hi all,

I am facing problem with NUM function,

My requirement is i have to trim the input value,after that i have to check if it is numeric or not.

for this i gave the derivation like this.

If Num(Trim(in_dataset.Member_Relationship_Cd)) then 1 else 0

when i use trim function along with NUM i am getting warning as
"Tfm_Stg1,0: Null string argument."

from soucre i am getting two spaces so if i trim those thing i will get empty then my output has to be one because empty is numeric value.

To overcome taht warning i tried removing trim function but that time my output is coimg as 0 because space is not a numeric.

can any one please help me.

Thanks for your help.

Regards

Swathi S

[chulett]

Add a specific IsNull check or perhaps try IsValid rather than Num.

[crazysaint]

Swathi,

Try something like this:
If Trim(in_dataset.Member_Relationship_Cd)="" Then 1 Else If Num(Trim(in_dataset.Member_Relationship_Cd)) then 1 else 0

Cheers,

K

[Mike]

Your assumption that Num would return true for an empty string is wrong.

Code: Select all

If Trim(in_dataset.Member_Relationship_Cd) = "" Then 1 Else Num(Trim(in_dataset.Member_Relationship_Cd))

should provide the result that you seem to be looking for.

Mike

[swathi Singamareddygari]

Hi Mike ,

Thanks for you replay

From datastage help i came to know that NUM function will return true if it is empty.

In the help its given like this.

Arg3 = " "
Boolean = Num(Arg3) ;* False (space is not numeric)
Arg4 = ""
Boolean = Num(Arg4) ;* True (empty string is numeric)

But when i tested it with empty value its returning false.


Regards

Swathi S

[Mike]

Did you look at the documentation in the Parallel Job Developer's Guide. What you found in help seems more likely to apply to the BASIC Num function.

In parallel, it's actually failing with a warning and you happen to get a default of 0 (false).

Mike

[swathi Singamareddygari]

Hi Mike,

I just saw the datastage help.In that one it says that empty is a numeric value.

Yes NUM function is giving warning with output as false.

can any one please help me how to check if the coulmn in numeric or not using other than NUM function.


Thanks for your help.

[chulett]

As suggested earlier, IsValid() should be another choice.

[panchusrao2656]

try using the function 'Convert'.

svar1= If IsNull(Input.Col) Then '' Else Convert('0123456789',' ',Input.Col); -- Convert all numbers to spaces
svar2=Trim(svar1," ","A") -- Trim all spaces
If svar2 = '' Then Non-Numeric Else Numeric

I am using similar logic to clean junk characters that are coming from the source in ZIP CODE field, TELEPHONE fields to pass only numbers to the next stage STAN(QualityStage).