RowProcCompareWithPreviousValue Routine not working...

kaps · Post by **kaps** » Fri Dec 07, 2007 1:54 pm

We are using this routine in a job where we concatenate three fields and check for the previous value of the same three fields. This one is not working at times...But If we use stage variable method it works.

Is there any restrictions on the length of charecters we pass into this routine ?

Appreciate your help...

kduke · Post by **kduke** » Fri Dec 07, 2007 5:42 pm

If you look at this routine it uses @USER0. This is sort of a global variable for a connection to DataStage. You need to copy this routine and use @USER1 and @USER2 so you can have 3 copies of this routine and they will not step on each other.

kaps · Post by **kaps** » Mon Dec 10, 2007 9:57 am

Kim

I have not used it in more than one place in a job. Do I need to do this even If I am using it in different job ? If so then I would need lot global variables like this as I have many different jobs using this.

Please let me know

Thanks

kduke · Post by **kduke** » Mon Dec 10, 2007 6:40 pm

You said you used it on 3 fields. Is this in one job? If so then you need to do what I suggested.

kaps · Post by **kaps** » Mon Dec 17, 2007 12:00 pm

Kim

What I meant is I need to concatanate 3 fields and get a value and compare it for next row. Only one time in a job.

Example

RowProcCompareWithPreviousValue(field1:filed2:filed3)

Thanks

ray.wurlod · Post by **ray.wurlod** » Mon Dec 17, 2007 1:42 pm

So we're back to what do you mean by "not working"?

kduke · Post by **kduke** » Tue Dec 18, 2007 9:27 pm

That should work.

I agree with Ray, I have no idea what is not working.

kaps · Post by **kaps** » Wed Dec 26, 2007 5:27 pm

"Not Working" meaning that the function does not find the difference between two rows even though they are different.

Example :

Row1 : 01223ret236792476r4yr80r84ry4y
Row2 : 01223ret236792476r4yr80r84ry4Z

I was expecting that this routine would say these are different as the last charecter differs. But Routine says that they are same. It appears that it did not check the last charecter and thats why I was asking if there is any restrictions on the length.

Thanks

chulett · Post by **chulett** » Wed Dec 26, 2007 6:29 pm

If there is a restriction, it's certainly not less than 30 characters. You aren't using Row Buffering, are you? That is contraindicated when using routines with COMMON storage, which this relies on.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 26, 2007 10:48 pm

The logic in the routine should work.

Code: Select all

If NewValue = LastValue Then
    	Ans = 1 
    End Else
    	Ans = 0
        LastValue = NewValue
    End

Just for interest, though, take a copy of the routine and replace the above logic with the following.

Code: Select all

If Compare(LastValue, NewValue, "L") = 0 Then
    	Ans = 1 
    End Else
    	Ans = 0
        LastValue = NewValue
    End

kaps · Post by **kaps** » Thu Dec 27, 2007 12:18 pm

Craig - This routine works till 29 char. If the input is 30 chars it does not work.

Ray - If I used your code it works fine even if it's more than 29 chars.
Can you tell me what's significance of Compare function ?

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Dec 27, 2007 2:16 pm

Comparison operators such as "=" must make decisions about whether the operands are numeric or not, so as to perform the correct type of comparison (string or numeric). The third argument of Compare() - the "justification" argument - allows this to be forced. The following code ought also to work.

Code: Select all

If "X" : NewValue = "X" :LastValue Then 
       Ans = 1 
    End Else 
       Ans = 0 
        LastValue = NewValue 
    End

chulett · Post by **chulett** » Thu Dec 27, 2007 4:36 pm

kaps wrote:Craig - This routine works till 29 char. If the input is 30 chars it does not work.

I know what you've said before, simply saying it again doesn't help.

My question was about row buffering, something you didn't answer. And the routine works fine for me - I just build a quick test job passing it strings from 20 to over 60 characters in length and all comparisons were correct regardless of length. This with 7.5.1a on an HP-UX server, so unless they borked it in 8.x or for your operating system, I'd still guess it is related to how you are using it, not with the routine itself.

ririr · Post by **ririr** » Fri Dec 28, 2007 11:06 am

Are the rows read from the source sorted?

order by Field1,Field2,Field3.

You will never get the comparison correctly if the source data is not ordered.

The routine has no restrictions on the length of the input string. Test the routine with sample data.

chulett · Post by **chulett** » Fri Dec 28, 2007 1:58 pm

The comparison itself would be valid regardless of how the input is sorted, however you are correct in that most people would use it with sorted input.