Based on a individual persons code (AGT_6) I am trying to return the office number for the next level of management. Starting at the bottom level AAA the next level up would be UNT. However, in some cases the UNT will be nul and it would role up to the MGR. Also, in other cases the person would already be at one of the higher levels and it would role up to the level above them. So regardless of the level of management I need to return the next level above.
The AAA is the bottom and has no office number. The green number would be the individuals office number and the yellow number would be the next level up that I need to return for each individual AGT_6 number.
AGT_6
Contract Type
UNT
MGR
TERR
RGN
759587
AAA
00A475520000
00A450030000
00A450000000
00V540010000
AGT_6
Contract Type
UNT
MGR
TERR
RGN
258746
UUU
00A596340000
00A698570000
00A3958740000
00V365874000
AGT_6
Contract Type
UNT
MGR
TERR
RGN
258746
MMM
nul
00A698570000
00A3958740000
00V365874000
AGT_6
Contract Type
UNT
MGR
TERR
RGN
175962
TTT
nul
nul
00A369700000
00V692710000
This would be the load statement I have to load all the data, What I want to be able to do is when i search by the AGT_6 I return the next level office number for that AGT_6.
SQL SELECT substr(AGT_CD,3,6) as AGT_6, “MGR_OFF_NO", "RGN_OFF_NO", "TERR_OFF_NO", "UNT_OFF_NO" FROM ABCD.MAGIC;
