intermediate result set required from a recordset

[talsahota]

I have the following recordset (2 columns oldpolid,newpolid). eg is as follows
a,b
b,c
c,d
d,e

I want the following record set
a,e
b,e
c.e
d.e

i.e. I need the final answer but also all the intemediate .
I would appreciate any help, thanks guys

[chucksmith]

Load the data to a hash file, then write a routine to do a recursive lookup to the same hash file. Recurse until a read fails. The last good read is your answer, then update the original hash file row with the final answer.

Code: Select all

Hash --> xfr (call routine) --> Hash

Be sure to disable caching.

[Sainath.Srinivasan]

Select all your records from the source table and perform a lookup old_pol_id with the following in the db stage.

select *
from
(
select
level l1, new_pol_id, old_pol_id
from
policy_table
start with old_pol_id = :1 -- incoming lookup value
connect by prior new_pol_id = old_pol_id
)
having l1 = max(l1)

By this way, you can find the most recent policy id for each value.

Note that if your target is the same table used for update, it must not commit until the whole job has finished as otherwise this lookup query might be disturbed.

It is better to put the result in a sequential file and then update the table.

[vmcburney]

You don't give a database type however the simplest generic SQL statement would be:

select oldpolid, max(newpolid)
from recordset
group by oldpolid

You can do the same thing in a DataStage Aggregation stage if you do not want to use a SQL group by.

[naren6876]

Hi
I am trying to write the routine to do a recursive lookup to the same hash file.But iam out of luck.Could anyone please help me to write a routine.

Thanks in advance.

Load the data to a hash file, then write a routine to do a recursive lookup to the same hash file. Recurse until a read fails. The last good read is your answer, then update the original hash file row with the final answer.

Code: Select all

Hash --> xfr (call routine) --> Hash

Be sure to disable caching.

[ray.wurlod]

Recursive functions are possible in server routines. Include a self-referential DEFFUN declaration. Beyond that, it's impossible to suggest anything without some kind of specification - what do you want to lookup recursively, and what is the rule that lets you exit from the recursion stack.

Dictionary definition of recursion:
recursion (n): see recursion

[naren6876]

Ray,

I have one column in my input which is a key,

col1
a

and My reference link data is

col1 col2 col3

a 4 b
b 4 c
c 4 d
d 1 e

where col1 is key.

My requirement is for my input record 'a' look for the value in reference data if it finds the key value , then it should check the col2 value=4 or not.
If the col2 value = 4 then take the value from the col3 which is 'b'.Then need to check the remang records in the refrence data for the 'b'.Here, we have the record with 'b' so take the value from the col3 which is 'c.
need to do this until col2<>4.

In the above scenario for input record 'a' i should get the output 'e'.

I hope i explain the requirement.

could you please suggest me to achieve the desired result.

Thanks in advance.

Recursive functions are possible in server routines. Include a self-referential DEFFUN declaration. Beyond that, it's impossible to suggest anything without some kind of specification - what do you want to lookup recursively, and what is the rule that lets you exit from the recursion stack.

Dictionary definition of recursion:
recursion (n): see recursion

[ray.wurlod]

Here's a sketch of the algorithm. Open the hashed file into a variable in COMMON if it's not already open. You will, as noted above, need a DEFFUN declaration.

Code: Select all

FUNCTION ReadRec(Arg)
DEFFUN ReadRec(X) Calling "DSU.ReadRec"
COMMON /ReadRec/Initialized, HashedFile.fvar
If Not(Initialized) Then Gosub OpenHashedFile  ; * I leave this to you

Read Rec From HashedFile.fvar, Arg
Then
   Col2 = Rec<1>
   Col3 = Rec<2>
   If Col2 = 4
      Then Ans = ReadRec(Col3)  ; * recursive call
      Else Ans = Col3
End
Else
   Ans = ""
End

RETURN(Ans)

OpenHashedFile:
   * Your code (standard Open/Openpath) here
Return(0)

[naren6876]

Ray,
Thank you very much for your help.
In the above routine, i want to capture the 'Col1' if Col2<>4.For that, what i did is:
Read Rec From HashedFile.fvar, Arg
Then
Col2 = Rec<1>
Col3 = Rec<2>
If Col2 = 4
Then Ans = ReadRec(Col3) ; * recursive call
Else Ans = Rec<0>

But i didn't get the Col1 value.

Any clues please here.

Thanks in advance.

Here's a sketch of the algorithm. Open the hashed file into a variable in COMMON if it's not already open. You will, as noted above, need a DEFFUN declaration.

Code: Select all

FUNCTION ReadRec(Arg)
DEFFUN ReadRec(X) Calling "DSU.ReadRec"
COMMON /ReadRec/Initialized, HashedFile.fvar
If Not(Initialized) Then Gosub OpenHashedFile  ; * I leave this to you

Read Rec From HashedFile.fvar, Arg
Then
   Col2 = Rec<1>
   Col3 = Rec<2>
   If Col2 = 4
      Then Ans = ReadRec(Col3)  ; * recursive call
      Else Ans = Col3
End
Else
   Ans = ""
End

RETURN(Ans)

OpenHashedFile:
   * Your code (standard Open/Openpath) here
Return(0)

[ray.wurlod]

Col1 is Arg at the innermost call. Lodge it in a COMMON or system variable such as @USER.RETURN.CODE.

[naren6876]

Ray,
How can i lodge it in a COMMON?.
you mean to say, declaring a variable at COMMON like file variable.If i declare a variable at COMMON, how can i assign the innermost key value to that variable.Please explain me.

Thanks in advance for your time.

Col1 is Arg at the innermost call. Lodge it in a COMMON or system variable such as @USER.RETURN.CODE.

[ray.wurlod]

Take a look at the hashed file's file variable (HashedFile.fvar). It's declared to be in COMMON, and is accessible at all recursion levels. Any variable in COMMON contains the value that was most recently assigned to it. Therefore, if you assign a value at the innermost level, it will be available at the outermost level. The system variables I mentioned (@USER0 through @USER4 and @USER.RETURN.CODE) are in a "special" COMMON area, so they work the same way.

[naren6876]

Ray,
I have declared the another variable 'var1' as you suggested in the COMMON.
I have assgned the value Rec<0> to the 'var1'. But, iam not getting the value.Please find the following results.

FUNCTION ReadRecord(Arg1)
DEFFUN ReadRecord(X) Calling "DSU.ReadRecord"
COMMON /ReadRecord/Initialized,HashedFile.fvar,var1

If Not(Initialized) Then OPEN '','recursive' TO HashedFile.fvar ELSE CALL DSLogFatal('oops','')
Read Rec From HashedFile.fvar, Arg1
Then
Col2 = Rec<1>
Col3 = Rec<2>
var1 = Rec<0>
If Col2 = 4
Then Ans = ReadRecord(Col3) ; * recursive call
Else Ans = var1
End
Else
Ans = ""
End
Return(Ans)

Test#1:
Arg1 = b

Test failed.

Program "TSTReadRecord.B": Line 4, COMMON size mismatch in subroutine "DSU.ReadRecord".
Program "TSTReadRecord.B": Line 4, Unable to load file "DSU.ReadRecord".
Program "TSTReadRecord.B": Line 4, Unable to load subroutine.

Take a look at the hashed file's file variable (HashedFile.fvar). It's declared to be in COMMON, and is accessible at all recursion levels. Any variable in COMMON contains the value that was most recently assigned to it. Therefore, if you assign a value at the innermost level, it will be available at the outermost level. The system variables I mentioned (@USER0 through @USER4 and @USER.RETURN.CODE) are in a "special" COMMON area, so they work the same way.

[ray.wurlod]

COMMON remains active while you're connected. Hence the error about changing its size. You need to disconnect your DataStage client and establish a new connection for the changed COMMON to be legal.

[naren6876]

Ray,
After reconnecting to the DS client also, i am not getting the expected value.I'm getting result as blank or might be empty space.
Am i missing anything here.

Thanks for your time.

COMMON remains active while you're connected. Hence the error about changing its size. You need to disconnect your DataStage client and establish a new connection for the changed COMMON to be legal.