Please suggest on the below scenario whether we need to create a View / Table and also suggest to me how to implement.
We have a Table as below
Risk ID Related Risk
1) As of now if the user wants to see the records of Risk ID 330335 - we are fetching only for that Risk ID in our Cognos Report.
2) Now the client requirement is we also need to fetch the Related Risk of that Risk ID.
3) For Ex- Risk ID 330335 - We need to fetch the records as below
A) Main Risk ID 330335
B) Related Risk of 330335 i.e 328664, 333025
C) Again we need to check for 328664, 333025 Risk ID's -
C.1) For 328664 we will get again the Related RIsk ID are 327986, 330335. Here we need to consider 327986 only as we already checked for 330335 in Step A
C.2) For 333025 we dont have any Related Risk ID
D) Again we need to check for 327986
D.1) So the Related risk for this is 328664 which is already checked in Step C.
This process of recursion of fetching the Related Risk ID's will be up to no new Related Risk ID's are there.