I’m not sure why this is causing me so much trouble or why I for some reason cannot get my brain wrapped around what i need to do here, but i have a large ORACLE database containing workorders, maintenance actions performed on those workorders, and parts replaced on maintenance actions. There are 2 tables I am concerned with in this instance, the MAINTENANCE_ACTION table and the ACTION_PART table. The MAINTENANCE_ACTION table is reflected below (TABLE NAMES, COLUMN NAMES, and DATA are examples and not actual data):
| WO_ID | MA_ID | POS | CORRECTIVE |
|---|---|---|---|
| AGH213 | POI321 | A23 | N |
| AGH213 | AGH562 | A23 | N |
| AGH213 | ABC567 | A25 | N |
| AGH213 | JIH123 | A23 | Y |
| AGH213 | UIH876 | A21 | N |
| AGH213 | PIA278 | A21 | N |
| AGH222 | PAO325 | A02 | N |
| AGH222 | DEF517 | A05 | Y |
| AGH222 | KJH133 | A06 | N |
| AGH222 | DEF517 | A05 | N |
| FAH555 | AAA235 | A00 | Y |
The ACTION_PART table is reflected below (FOM means the part was removed and reinstalled):
| MA_ID | POS | PART | CAGE | SERIAL | ACTION | FOM |
|---|---|---|---|---|---|---|
| POI321 | A23 | BLA43 | 123 | 111 | REMOVE | N |
| AGH562 | A23 | BLA32 | 123 | 333 | INSTALL | N |
| ABC567 | A25 | SHI2 | 451 | NULL | INSPECT | N |
| JIH123 | A23 | NULL | NULL | NULL | NULL | N |
| UIH876 | A21 | SHI3 | 451 | 003 | INSTALL | N |
| PIA278 | A21 | SHI3 | 451 | 001 | REMOVE | N |
| PAO325 | A02 | ABC12 | 552 | 010 | REMOVE | Y |
| DEF517 | A05 | ABC12 | 552 | 011 | REMOVE | N |
| KJH133 | A06 | NULL | NULL | NULL | NULL | N |
| DEF517 | A05 | CBA13 | 652 | 120 | INSTALL | N |
| AAA235 | A99 | 222ABC | 851 | 001 | INSTALL | N |
What I would like my query result to display is:
| WO_ID | POS | REM_PART | REM_CAGE | REM_SER | INS_PART | INS_CAGE | INS_SER | FOM |
|---|---|---|---|---|---|---|---|---|
| AGH213 | A23 | BLA43 | 123 | 111 | BLA32 | 123 | 333 | N |
| AGH213 | A21 | SHI3 | 451 | 001 | SHI3 | 451 | 003 | N |
| AGH222 | A02 | ABC12 | 552 | 010 | Y | |||
| AGH222 | A05 | ABC12 | 552 | 011 | CBA13 | 652 | 120 | N |
| FAH555 | A99 | 222ABC | 851 | 001 | N |
Notes: all info from this query will be LEFT JOIN’d to another query based ON WO_ID, the script is ran through SQLplus.
POS will be the POS the part was removed or installed from not the POS on the MAINTENANCE_ACTION
the removed part and installed part should have the same POS
there may be a removed part without an installed part or vice-versa, FOM flag does not affect this.
the removed part should not be the same as the installed part
I hope I am not forgetting any details here, I’m just hoping I can get some ideas on this. I’m not good with cursor’s or loops, if that is what i need to do here just keep in mind this is being joined to a much larger query with lots of additional data being joined into it… It’s a little ridiculous to be honest but unfortunately the only way i can get everything needed into this report for this purpose, the script will be on a schedule task so it’s fine if it takes a minute to run but it can’t be too excessive and take up all the resources.
also note this is on a system that cannot install any third party tools or extensions, I am limited to spooling results into a .txt from SQLplus. The script is run on different ORACLE databases from 11g to 19c.
1