I need to loop through the Sql table until a certain condition is met

I am trying to find a supervisor ID of an employee based on the organization unit.

Table A: Employee

EmpID	BeginDate	EndDate	OrgUnit
1001	20070618	20070624	5001

Table B: OrgUnit

OrgType	OrgUnit	Code	Status	BeginDate	EndDate	SID
O	5001	B	008	20050101	20160619	4002
O	5001	A	002	20050101	20110201	4001
O	4001	B	008	20070618	20070624	4110
O	4001	A	002	20070618	20070624	4003
O	4003	B	012	20070618	20070624	4444	- Supervisor ID

Step 1: Get the Org Unit from Table A for a Emp ID = 1001
Step 2: Find a match for Org Unit in Table B and get SID for status B012
Step 3: If the value does not exist in Step 2, check for the status, A 002, get the new SID
Step 4: If the value exists from Step 3, get new SID and for status B012 in the Table B where OrgUnit = SID (from Step 3)
Step 5: If value from Step 4 not exists, Search it for status A002 where OrgUnit = SID (Step 4) and get the new SID from table B
Step 6: If value from Step 5 exists,Get new SID for status B 012 for OrgUnit = SID (from Step 5)
Step 7: If value exists from step 6, get new SID for status 008 in Table B where OrgUnit = SID (from Step6)

Note: Steps 3, 4, 5 are kinda loop scenario until we have a SID for status B 012.

I am able to get the data for happy path. In case of data not found not sure how to match up SID against Table B for different code-status. I am trying not to have a cursor and loop through every record to compare.

Solution

Logic is somethat complicated, answer looks not so hard.

with o as (select orgunit, code||status code, sid from orgunit 
           where (code, status) in (('A', '002'), ('B', '012')) )
select o1.orgunit,
       case when o1.code = 'B012' then o1.sid
            when o1.code = 'A002' and o2.code = 'B012' then o2.sid
            when o1.code = 'A002' and o2.code = 'A002' and o3.code = 'B012' 
            then o3.sid
       end supervisor
from o o1
left join o o2 on o1.sid = o2.orgunit 
left join o o3 on o2.sid = o3.orgunit 
where o1.orgunit = (select orgunit from employee where empid = 1001)

dbfiddle demo

This solution is tested in Oracle platform, but is close to standard, except maybe concatenation, so you should be able translate it to any SQL dialect easily. I hope it will work, these are just three left joins and one case. In case you had unknown number of loops you would need recursive solution.

Edit: In your dbfiddle provided in comments you have the case, where employee has two paths:

ORGUNIT CODE    SID ORGUNIT CODE    SID 
2014    A002    120 120     A002    116   <-- not supervisor, null returned
2014    A002    120 120     B012    7733  <-- supervisor

In this case you can use simply min or max over case statement and group by orgunit dbfiddle.

But we can imagine, that second path leads to another supervisor:

ORGUNIT CODE    SID ORGUNIT CODE    SID ORGUNIT CODE    SID 
2014    A002    120 120     A002    116 116     B012    7775 <-- another  supervisor
2014    A002    120 120     B012    7733  <-- supervisor

If such situation is possible we have ambiguity and you have to decide what to do, it doesn't matter if you use something similar to my query or write procedure.