Hello
Have a peculiar scenario, hoping for some guidance.
Two tables, Parentjob and Childjob, the parent job contains job and its corresponding instruction on how to do the job, while the child contains most common set of instruction used.
The instruction column in “parentjob” table contains a pointer to the childjob table, which should be substituted in order to get complete instructions.
with q_Parentjob as
(
select 1 job_num,'.INSERT BTSH28 ++NEWLINE++ .INSERT JOB ++NEWLINE++ ++NEWLINE++' INSTRUCTION from dual UNION ALL
select 2 job_num,'.INSERT JOB' INSTRUCTION from dual UNION ALL
select 3 job_num,'SU/PERVISOR MUST SIGN AND DATE THIS SHEET ++NEWLINE++ .INSERT JOB' INSTRUCTION from dual UNION ALL
select 4 job_num,'++NEWLINE++' INSTRUCTION from dual UNION ALL
select 5 job_num,'.INSERT JOB' INSTRUCTION from dual UNION ALL
select 6 job_num,'.INSERT BTSH28 ++NEWLINE++ .INSERT JOB ++NEWLINE++ .INSERT BTDR29 ++NEWLINE++ .INSERT BTDR739 ++NEWLINE++ .INSERT BTDZ43' INSTRUCTION from dual
)
, q_childjob as
(
select '.INSERT BTSH28' CHILD_INST, 'DETAIL INSTRUCTION HERE' DESCRIPTION from dual UNION ALL
select '.INSERT JOB','JOB INSTRUCTION HERE' from dual UNION ALL
select '.INSERT BTDR29','WEAK BOARDING LADDER' from dual UNION ALL
select '.INSERT BTDR739','lube room has ol leak' from dual UNION ALL
select '.INSERT BTDZ43','swing is rought' from dual
)
select * from q_childjob ;
select * from q_Parentjob;
Output: The “CHILD_INST” coulmn values from Childjob table can occur anywhere in Parentjob.Instruction column
Thanks