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;