intermediate result set required from a recordset

[Sunshine2323]

Hi,

I have used Ray's code and got the expected output you require.

First Load your input data in a Hash File say HASH_RECURSIVE

Create a Transform Function ReadRec as follows:

Code: Select all

DEFFUN ReadRec(X) Calling "DSU.ReadRec"
      COMMON /ReadRec/Initialized, HASH_RECURSIVE.fvar
      If Not(Initialized) Then Gosub OpenHashedFile          ; 
      Read Rec From Handle, Arg1
      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:
      OPEN "HASH_RECURSIVE" TO Handle
      THEN
         Call DSLogInfo("NO Error in Open","Recursive LookUp")
      END ELSE
         ErrorCode = 1
         Call DSLogInfo("Error in Open","Recursive LookUp")
         GOTO MainExit
      END

 Return(0)

  
MainExit:

Hope this Helps

[naren6876]

Hi,
I want to captur the innermost 'Col1' value not the 'Col3'.

Thanks for your time.

Hi,

I have used Ray's code and got the expected output you require.

First Load your input data in a Hash File say HASH_RECURSIVE

Create a Transform Function ReadRec as follows:

Code: Select all

DEFFUN ReadRec(X) Calling "DSU.ReadRec"
      COMMON /ReadRec/Initialized, HASH_RECURSIVE.fvar
      If Not(Initialized) Then Gosub OpenHashedFile          ; 
      Read Rec From Handle, Arg1
      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:
      OPEN "HASH_RECURSIVE" TO Handle
      THEN
         Call DSLogInfo("NO Error in Open","Recursive LookUp")
      END ELSE
         ErrorCode = 1
         Call DSLogInfo("Error in Open","Recursive LookUp")
         GOTO MainExit
      END

 Return(0)

  
MainExit:

Hope this Helps

[Sunshine2323]

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'.

This requirement is met by the routine above.

[naren6876]

Hi Sunshine,
Yes. you are right.But my requirement is slightly changed and i need to get the 'Col1' value if col2<>4.

Thanks for your time.

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'.

This requirement is met by the routine above.

[Sunshine2323]

Hi Naren,

Only change in the routine is Ans=Arg1 in the Else part of the 2nd IF statement

Code: Select all

DEFFUN ReadRec(X) Calling "DSU.ReadRec"
      COMMON /ReadRec/Initialized, HASH_RECURSIVE
      If Not(Initialized) Then Gosub OpenHashedFile ; 
      Read Rec From Handle, Arg1
      Then
         Col2 = Rec<1>
         Col3 = Rec<2>
         If Col2 = 4
            Then Ans = ReadRec(Col3)     ; * recursive call
            Else Ans = Arg1
      End
      Else
         Ans = ""
      End

      RETURN(Ans)

OpenHashedFile:
* Your code (standard Open/Openpath) here
      OPEN "HASH_RECURSIVE" TO Handle
      THEN
         Call DSLogInfo("NO Error in Open","TransformationJobs")
      END ELSE
         ErrorCode = 1
         Call DSLogInfo("Error in Open","TransformationJobs")
         GOTO MainExit
      END

      Return(0)


MainExit: