I have a query that is almost exactly what I need, however I need to add a Column to the end where I have the dummy field of active account to display the account the has '_Sys' from which ever of the OACT (T1, T2, or T3) AcctCode fields that matches that criteria.
Any help would be greatly appreciated.
* Only 1 of the fields will ever contain the '_Sys' code.
Best of luck,
(Select T0.AcctCode, T0.AcctName, T0.Levels, T0.GrpLine, T0.FatherNum, T0.GroupMask,
T3.AcctCode, T3.GroupMask, T3.FormatCode,
'Active Account' as 'Active Account'
from OACT T0
Left Outer Join OACT T1 on T1.FatherNum = T0.AcctCode
Left Outer Join OACT T2 on T2.FatherNum = T1.AcctCode
Left Outer Join OACT T3 on T3.FatherNum = T2.AcctCode
Where T0.Levels = 1)
Order By T0.GroupMask