basically I have a audit table like the following.
| JOB_NO | DEPENDANT_JOB_NO | DEPENDANT_SUB_JOB_NO |
|---|---|---|
| Job_198 | Job_551 | BDP2_Sjob_48 |
| Job_24 | Job_551 | BDP2_Sjob_23 |
| BIL_Job_10 | Job_5 | Sjob_6 |
I need to develop a recursive cte where it should list of like
| JOB_NO | DEPENDANT_JOB_NO | DEPENDANT_SUB_JOB_NO |
|---|---|---|
| Job_198 | Job_551 | BDP2_Sjob_48 |
| Job_551 | -- | - |
| BDP2_Sjob_48 | - | - |
This is just for reference for only JOB_198.
I tried the below code but the results are no expected.
with recursive rec_cte as (
select JOB_NO,DEPENDANT_JOB_NO,DEPENDANT_SUB_JOB_NO
from CTL_TALEND_JOBS_METADATA
where JOB_NO ='Job_198'
union all
select p.DEPENDANT_JOB_NO,h.DEPENDANT_JOB_NO,h.DEPENDANT_SUB_JOB_NO
from rec_cte p
inner join CTL_TALEND_JOBS_METADATA h
on (h.JOB_NO = p.DEPENDANT_JOB_NO)
),
rec_cte1 as (
select JOB_NO,DEPENDANT_JOB_NO,DEPENDANT_SUB_JOB_NO
from CTL_TALEND_JOBS_METADATA
where JOB_NO ='Job_198'
union all
select p.DEPENDANT_SUB_JOB_NO,h.DEPENDANT_JOB_NO,h.DEPENDANT_SUB_JOB_NO
from rec_cte1 p
inner join CTL_TALEND_JOBS_METADATA h
on (h.JOB_NO = REPLACE(p.DEPENDANT_SUB_JOB_NO,'BDP2_Sj','J'))
)
select * from rec_cte
union
select * from rec_cte1;
The results of the query ->
JOB_NO DEPENDANT_JOB_NO DEPENDANT_SUB_JOB_NO
Job_41 Job_19
Job_41 Job_31
Job_41 Job_32
Job_41 Job_37
Job_19 Job_551 BDP2_Sjob_15
Job_31 Job_13
Job_32 Job_9
Job_37 Job_260
Job_13 Job_551 BDP2_Sjob_8
Job_260 Job_551 BDP2_Sjob_50
Job_551
Job_9 Job_551 BDP2_Sjob_5
I have added the data below.
JOB_NO DEPENDANT_JOB_NO SUB_DEPANDANT_JOB_NO
Job_198 Job_551 BDP2_Sjob_48
Job_24 Job_551 BDP2_Sjob_23
BIL_Job_10 Job_5 Sjob_6
BIL_Job_106 Job_5 Sjob_40
BIL_Job_109 Job_5 Sjob_39
BIL_Job_11 Job_5 Sjob_3
BIL_Job_12 Job_5 Sjob_7
BIL_Job_13 Job_5 Sjob_8
BIL_Job_14 Job_5 Sjob_10
BIL_Job_15 Job_5 Sjob_11
BIL_Job_16 Job_5 Sjob_9
BIL_Job_169 Job_5 Sjob_45
BIL_Job_18 Job_5 Sjob_20
BIL_Job_188 Job_5 Sjob_46
BIL_Job_19 Job_5 Sjob_15
BIL_Job_20 Job_5 Sjob_16
BIL_Job_21 Job_5 Sjob_19
BIL_Job_22 Job_5 Sjob_17
BIL_Job_23 Job_5 Sjob_18
BIL_Job_236 Job_5 Sjob_49
BIL_Job_26 Job_5 Sjob_13
BIL_Job_260 Job_5 Sjob_50
BIL_Job_27 Job_5 Sjob_12
BIL_Job_29 Job_5 Sjob_14
BIL_Job_373 Job_5 Sjob_53
BIL_Job_49 Job_5 Sjob_24
BIL_Job_50 Job_5 Sjob_26
BIL_Job_502 Job_5 Sjob_54
BIL_Job_505 Job_5 Sjob_55
BIL_Job_506 Job_5 Sjob_56
BIL_Job_508 Job_5 Sjob_57
BIL_Job_52 Job_5 Sjob_29
BIL_Job_53 Job_5 Sjob_30
BIL_Job_7 Job_5 Sjob_22
BIL_Job_72 Job_5 Sjob_32
BIL_Job_73 Job_5 Sjob_31
BIL_Job_76 Job_5 Sjob_34
BIL_Job_8 Job_5 Sjob_1
BIL_Job_80 Job_5 Sjob_36
BIL_Job_82 Job_5 Sjob_35
BIL_Job_9 Job_5 Sjob_5
Job_1
Job_10 Job_551 BDP2_Sjob_6
Job_100 Job_97
Job_101 Job_98
Job_102 Job_99
Job_103 Job_100
Job_104 Job_101
Job_105 Job_102
Job_105 Job_464
Job_105 Job_462
Job_106 Job_551 BDP2_Sjob_40
Job_107
Job_108 Job_107
Job_109 Job_551 BDP2_Sjob_39
Job_11 Job_551 BDP2_Sjob_3
Job_110 Job_106
Job_111 Job_543
Job_111 Job_108
Job_112 Job_109
Job_112 Job_45
Job_114 Job_118
Job_114 Job_74
Job_115 Job_56
Job_115 Job_71
Job_115 Job_460
Job_115 Job_461
Job_116 Job_115
Job_116 Job_35
Job_116 Job_36
Job_116 Job_112
Job_116 Job_488
Job_116 Job_515
Job_116 Job_514
Job_116 Job_513
Job_116 Job_574
Job_117 Job_116
Job_118 Job_74
Job_118 Job_64
Job_119 Job_114
Job_12 Job_551 BDP2_Sjob_7
Job_120 Job_74
Job_120 Job_36
Job_120 Job_115
Job_120 Job_118
Job_121 Job_44
Job_121 Job_115
Job_121 Job_118
Job_122 Job_115
Job_122 Job_45
Job_122 Job_44
Job_122 Job_36
Job_122 Job_118
Job_123 Job_115
Job_123 Job_43
Job_123 Job_81
Job_123 Job_118
Job_124 Job_120
Job_124 Job_121
Job_124 Job_122
Job_124 Job_123
Job_125 Job_115
Job_125 Job_74
Job_125 Job_44
Job_125 Job_119
Job_125 Job_124
Job_125 Job_118
Job_126 Job_45
Job_126 Job_115
Job_126 Job_36
Job_126 Job_44
Job_126 Job_124
Job_126 Job_118
Job_127 Job_115
Job_127 Job_44
Job_127 Job_124
Job_127 Job_118
Job_128 Job_115
Job_128 Job_44
Job_128 Job_124
Job_128 Job_118
Job_129 Job_125
Job_129 Job_126
Job_129 Job_127
Job_129 Job_128
Job_13 Job_551 BDP2_Sjob_8
Job_130 Job_119
Job_130 Job_34
Job_130 Job_124
Job_130 Job_129
Job_130 Job_118
Job_131 Job_115
Job_131 Job_119
Job_131 Job_43
Job_131 Job_124
Job_131 Job_129
Job_131 Job_118
Job_132 Job_44
Job_132 Job_115
Job_132 Job_124
Job_132 Job_129
Job_132 Job_118
Job_133 Job_48
Job_133 Job_115
Job_133 Job_124
Job_133 Job_129
Job_133 Job_118
Job_134 Job_119
Job_134 Job_44
Job_134 Job_115
Job_134 Job_124
Job_134 Job_129
Job_134 Job_118
Job_135 Job_115
Job_135 Job_81
Job_135 Job_124
Job_135 Job_129
Job_135 Job_118
Job_136 Job_115
Job_136 Job_48
Job_136 Job_124
Job_136 Job_129
Job_136 Job_118
Job_137 Job_130
Job_137 Job_131
Job_137 Job_132
Job_137 Job_133
Job_137 Job_134
Job_137 Job_135
Job_137 Job_136
Job_138 Job_119
Job_138 Job_124
Job_138 Job_129
Job_138 Job_137
Job_138 Job_118
Job_139 Job_119
Job_139 Job_115
Job_139 Job_124
Job_139 Job_129
Job_139 Job_137
Job_139 Job_118
Job_14 Job_551 BDP2_Sjob_10
Job_140 Job_115
Job_140 Job_119
Job_140 Job_124
Job_140 Job_129
Job_140 Job_137
Job_140 Job_118
Job_141 Job_115
Job_141 Job_119
Job_141 Job_124
Job_141 Job_129
Job_141 Job_137
Job_141 Job_118
Job_142 Job_44
Job_142 Job_115
Job_142 Job_124
Job_142 Job_129
Job_142 Job_137
Job_142 Job_118
Job_143 Job_115
Job_143 Job_119
Job_143 Job_124
Job_143 Job_129
Job_143 Job_137
Job_143 Job_118
Job_144 Job_115
Job_144 Job_119
Job_144 Job_124
Job_144 Job_129
Job_144 Job_137
Job_144 Job_118
Job_145 Job_115
Job_145 Job_81
Job_145 Job_124
Job_145 Job_129
Job_145 Job_137
Job_145 Job_118
Job_146 Job_115
Job_146 Job_48
Job_146 Job_124
Job_146 Job_129
Job_146 Job_137
Job_146 Job_118
Job_147 Job_138
Job_147 Job_139
Job_147 Job_140
Job_147 Job_141
Job_147 Job_142
Job_147 Job_143
Job_147 Job_144
Job_147 Job_145
Job_147 Job_146
Job_147 Job_573
Job_148 Job_115
Job_148 Job_110
Job_148 Job_124
Job_148 Job_129
Job_148 Job_137
Job_148 Job_147
Job_148 Job_118
Job_149 Job_115
Job_149 Job_81
Job_149 Job_124
Job_149 Job_129
Job_149 Job_137
Job_149 Job_147
Job_15 Job_551 BDP2_Sjob_11
Job_150 Job_115
Job_150 Job_111
Job_150 Job_124
Job_150 Job_129
Job_150 Job_137
Job_150 Job_147
Job_151 Job_115
Job_151 Job_36
Job_151 Job_74
Job_151 Job_124
Job_151 Job_129
Job_151 Job_137
Job_151 Job_147
Job_152 Job_115
Job_152 Job_40
Job_152 Job_41
Job_152 Job_124
Job_152 Job_129
Job_152 Job_137
Job_152 Job_147
Job_153 Job_148
Job_153 Job_149
Job_153 Job_150
Job_153 Job_151
Job_153 Job_152
Job_153 Job_162
Job_153 Job_197
Job_154 Job_115
Job_154 Job_124
Job_154 Job_129
Job_154 Job_137
Job_154 Job_147
Job_154 Job_153
Job_155 Job_115
Job_155 Job_43
Job_155 Job_124
Job_155 Job_129
Job_155 Job_137
Job_155 Job_147
Job_155 Job_153
Job_156 Job_115
Job_156 Job_44
Job_156 Job_124
Job_156 Job_129
Job_156 Job_137
Job_156 Job_147
Job_156 Job_153
Job_156 Job_118
Job_156 Job_548
Job_157 Job_115
Job_157 Job_112
Job_157 Job_124
Job_157 Job_129
Job_157 Job_137
Job_157 Job_147
Job_157 Job_153
Job_158 Job_115
Job_158 Job_71
Job_158 Job_124
Job_158 Job_129
Job_158 Job_137
Job_158 Job_147
Job_158 Job_153
Job_159 Job_115
Job_159 Job_71
Job_159 Job_36
Job_159 Job_124
Job_159 Job_129
Job_159 Job_137
Job_159 Job_147
Job_159 Job_153
Job_16 Job_551 BDP2_Sjob_9
Job_160 Job_115
Job_160 Job_119
Job_160 Job_35
Job_160 Job_124
Job_160 Job_129
Job_160 Job_137
Job_160 Job_147
Job_160 Job_153
Job_160 Job_118
Job_161 Job_154
Job_161 Job_155
Job_161 Job_156
Job_161 Job_157
Job_161 Job_158
Job_161 Job_159
Job_161 Job_160
Job_162 Job_48
Job_162 Job_115
Job_162 Job_124
Job_162 Job_129
Job_162 Job_137
Job_162 Job_147
Job_164 Job_161
Job_168 Job_Tealium_ES_01_P
Job_168 Job_36
Job_168 Job_30
Job_169 Job_551 BDP2_Sjob_45
Job_17 Job_5 Sjob_2
Job_170 Job_169
Job_171 Job_Tealium_AS_01_P
Job_172 Job_171
Job_172 Job_168
Job_173 Job_172
Job_173 Job_37
Job_174 Job_168
Job_174 Job_173
Job_175 Job_174
Job_175 Job_44
Job_175 Job_34
Job_175 Job_173
Job_176 Job_117
Job_176 Job_115
Job_177 Job_176
Job_178 Job_88
Job_178 Job_115
Job_178 Job_116
Job_179 Job_178
Job_18 Job_551 BDP2_Sjob_20
Job_180 Job_179
Job_181 Job_164
Job_181 Job_88
Job_181 Job_91
Job_181 Job_115
Job_181 Job_116
Job_182 Job_181
Job_183 Job_182
Job_184 Job_174
Job_184 Job_75
Job_185 Job_184
Job_185 Job_44
Job_186 Job_174
Job_186 Job_35
Job_187 Job_75
Job_188 Job_551 BDP2_Sjob_46
Job_189 Job_188
Job_19 Job_551 BDP2_Sjob_15
Job_190 Job_187
Job_190 Job_105
Job_190 Job_189
Job_190 Job_34
Job_190 Job_36
Job_190 Job_64
Job_190 Job_66
Job_191 Job_185
Job_191 Job_64
Job_192 Job_174
Job_192 Job_64
Job_192 Job_175
Job_193 Job_191
Job_193 Job_192
Job_193 Job_171
Job_193 Job_175
Job_197 Job_44
Job_197 Job_115
Job_197 Job_118
BIL_Job_198 Job_5 Sjob_48
Job_199 Job_198
Job_20 Job_551 BDP2_Sjob_16
Job_200 Job_5 Sjob_47
Job_201 Job_200
Job_207
Job_208
Job_209
Job_21 Job_551 BDP2_Sjob_19
Job_210
Job_211 Job_171
Job_211 Job_207
Job_211 Job_208
Job_211 Job_209
Job_211 Job_210
Job_212 Job_211
Job_212 Job_207
Job_213 Job_211
Job_213 Job_208
Job_214 Job_211
Job_214 Job_209
Job_215 Job_211
Job_215 Job_210
Job_216 Job_36
Job_216 Job_35
Job_217 Job_81
Job_217 Job_118
Job_218 Job_81
Job_218 Job_118
Job_219 Job_118
Job_219 Job_119
Job_219 Job_36
Job_219 Job_35
Job_219 Job_34
Job_22 Job_551 BDP2_Sjob_17
Job_220 Job_118
Job_220 Job_44
Job_221 Job_217
Job_221 Job_218
Job_221 Job_219
Job_221 Job_220
Job_221 Job_115
Job_221 Job_164
Job_222 Job_261
Job_222 Job_37
Job_222 Job_171
Job_222 Job_239
Job_222 Job_527
Job_223 Job_164
Job_224 Job_164
Job_225 Job_164
Job_226 Job_164
Job_227 Job_216
Job_228 Job_221
Job_229 Job_222
Job_23 Job_551 BDP2_Sjob_18
Job_230 Job_223
Job_231 Job_224
Job_231 Job_233
Job_232 Job_225
Job_232 Job_230
Job_233 Job_226
Job_233 Job_232
Job_234 Job_186
Job_235 Job_193
Job_236 Job_551 BDP2_Sjob_49
Job_237 Job_236
Job_237 Job_37
Job_238
Job_239 Job_212
BIL_Job_24 Job_1
Job_240
Job_241 Job_240
Job_242 Job_241 Job_240
Job_243 Job_199
Job_244
Job_245 Job_244
Job_246 Job_245 Job_243
Job_247
Job_248 Job_247
Job_249 Job_248
Job_25 Job_5 Sjob_21
Job_250
Job_251 Job_250
Job_252 Job_251
Job_253
Job_254 Job_253
Job_255 Job_254
Job_256 Job_254 Job_253
Job_26 Job_551 BDP2_Sjob_13
Job_260 Job_551 BDP2_Sjob_50
Job_262 Job_238 Sjob_UCCE_2
Job_263 Job_238 Sjob_UCCE_8
Job_264 Job_238 Sjob_UCCE_1
Job_265 Job_238 Sjob_UCCE_16
Job_267 Job_238 Sjob_UCCE_11
Job_268 Job_238 Sjob_UCCE_14
Job_269 Job_238 Sjob_UCCE_21
Job_27 Job_551 BDP2_Sjob_12
Job_270 Job_238 Sjob_UCCE_18
Job_271 Job_238 Sjob_UCCE_19
Job_272 Job_238 Sjob_UCCE_15
Job_273 Job_238 Sjob_UCCE_22
Job_275 Job_238 Sjob_UCCE_21
Job_276 Job_238 Sjob_UCCE_3
Job_277 Job_238 Sjob_UCCE_4
Job_279 Job_238 Sjob_UCCE_23
Job_28 Job_5 Sjob_4
Job_281 Job_238 Sjob_UCCE_7
Job_282 Job_238 Sjob_UCCE_2
Job_285 Job_415 Sjob_VRNT_1
Job_286 Job_415 Sjob_VRNT_2
Job_287 Job_415 Sjob_VRNT_3
Job_288 Job_415 Sjob_VRNT_4
Job_289 Job_415 Sjob_VRNT_5
Job_29 Job_551 BDP2_Sjob_14
Job_290 Job_415 Sjob_VRNT_6
Job_291 Job_415 Sjob_VRNT_7
Job_292 Job_415 Sjob_VRNT_8
Job_293 Job_415 Sjob_VRNT_9
Job_294 Job_415 Sjob_VRNT_26
Job_295 Job_415 Sjob_VRNT_27
Job_296 Job_415 Sjob_VRNT_28
Job_298 Job_415 Sjob_VRNT_30
Job_299 Job_415 Sjob_VRNT_10
Job_30 Job_29
Job_300 Job_415 Sjob_VRNT_35
Job_301 Job_415 Sjob_VRNT_12
Job_302 Job_415 Sjob_VRNT_13
Job_303 Job_415 Sjob_VRNT_14
Job_304 Job_415 Sjob_VRNT_15
Job_305 Job_415 Sjob_VRNT_16
Job_306 Job_415 Sjob_VRNT_17
Job_307 Job_415 Sjob_VRNT_19
Job_309 Job_415 Sjob_VRNT_20
Job_31 Job_13
Job_310 Job_415 Sjob_VRNT_21
Job_312 Job_415 Sjob_VRNT_31
Job_313 Job_415 Sjob_VRNT_23
Job_314 Job_415 Sjob_VRNT_24
Job_315 Job_415 Sjob_VRNT_25
Job_316 Job_415 Sjob_VRNT_32
Job_317 Job_416 Sjob_UWF_1
Job_318 Job_420 Sjob_SRVN_1
Job_319 Job_263
Job_319 Job_264
Job_32 Job_9
Job_321
Job_323
Job_325
Job_326
Job_33 Job_11
Job_330
Job_331
Job_332
Job_333
Job_334 Job_267
Job_334 Job_281
Job_335 Job_332
Job_336 Job_333
Job_337 Job_277
Job_338 Job_268
Job_338 Job_319
Job_338 Job_344
Job_338 Job_352
Job_339
Job_34 Job_8
Job_34 Job_10
Job_34 Job_373
Job_340 Job_415 Sjob_VRNT_33
Job_341 Job_415 Sjob_VRNT_34
Job_343 Job_301
Job_344 Job_282
Job_345 Job_300
Job_346 Job_306
Job_347 Job_285
Job_348 Job_286
Job_348 Job_352
Job_348 Job_347
Job_348 Job_343
Job_348 Job_302
Job_349 Job_272
Job_349 Job_273
Job_349 Job_279
Job_349 Job_317
Job_35 Job_26
Job_35 Job_36
Job_352 Job_289
Job_352 Job_290
Job_352 Job_299
Job_352 Job_301
Job_352 Job_303
Job_352 Job_304
Job_352 Job_340
Job_352 Job_310
Job_352 Job_312
Job_352 Job_313
Job_352 Job_314
Job_352 Job_341
Job_352 Job_315
Job_352 Job_288
Job_353 Job_330
Job_354 Job_262
Job_354 Job_319
Job_354 Job_344
Job_354 Job_352
Job_355 Job_272
Job_355 Job_319
Job_355 Job_334
Job_355 Job_337
Job_355 Job_349
Job_355 Job_451
Job_355 Job_352
Job_356 Job_265
Job_356 Job_319
Job_356 Job_352
Job_357 Job_269
Job_357 Job_319
Job_357 Job_334
Job_357 Job_352
Job_358 Job_270
Job_358 Job_337
Job_359 Job_292
Job_359 Job_309
Job_359 Job_346
Job_359 Job_293
Job_359 Job_345
Job_36 Job_27
Job_360 Job_271
Job_360 Job_337
Job_360 Job_334
Job_362 Job_275
Job_362 Job_334
Job_363 Job_273
Job_365 Job_318
Job_365 Job_349
Job_365 Job_319
Job_365 Job_352
Job_365 Job_419
Job_366 Job_317
Job_366 Job_319
Job_366 Job_349
Job_366 Job_367
Job_366 Job_399
Job_366 Job_352
Job_367 Job_317
Job_368 Job_286
Job_368 Job_352
Job_368 Job_347
Job_368 Job_343
Job_368 Job_302
Job_368 Job_287
Job_369 Job_305
Job_369 Job_352
Job_369 Job_347
Job_369 Job_343
Job_369 Job_302
Job_369 Job_307
Job_37 Job_260
Job_370 Job_288
Job_370 Job_341
Job_370 Job_340
Job_371 Job_298
Job_372 Job_295
Job_372 Job_296
Job_372 Job_435
Job_372 Job_436
Job_372 Job_438
Job_372 Job_371
Job_372 Job_430
Job_372 Job_352
Job_372 Job_349
Job_373 Job_551 BDP2_Sjob_53
Job_374 Job_375
Job_374 Job_376
Job_374 Job_378
Job_375 Job_400
Job_376 Job_400
Job_378 Job_400
Job_379 Job_378
Job_379 Job_375
Job_379 Job_376
Job_379 Job_374
Job_38 Job_17
Job_380 Job_326
Job_381 Job_380
Job_382 Job_325
Job_383 Job_382
Job_384 Job_321
Job_386 Job_323
Job_387 Job_413
Job_388 Job_387
Job_389 Job_386
Job_39 Job_28
Job_390 Job_407
Job_391 Job_390
Job_392 Job_339
Job_393 Job_331
Job_394 Job_393
Job_395 Job_398
Job_396 Job_335
Job_397 Job_336
Job_398 Job_392
Job_398 Job_396
Job_398 Job_381
Job_398 Job_391
Job_398 Job_352
Job_398 Job_441
Job_398 Job_349
Job_398 Job_37
Job_399 Job_317
Job_40 Job_20
Job_40 Job_31
Job_40 Job_32
Job_40 Job_37
Job_40 Job_34
Job_400
Job_407
Job_41 Job_19
Job_41 Job_31
Job_41 Job_32
Job_41 Job_37
Job_413
Job_415
Job_416
Job_417 Job_353
Job_417 Job_391
Job_417 Job_388
Job_417 Job_383
Job_417 Job_349
Job_417 Job_381
Job_417 Job_397
Job_417 Job_389
Job_417 Job_441
Job_417 Job_394
Job_417 Job_352
Job_419 Job_318
Job_42 Job_18
Job_42 Job_37
Job_42 Job_36
Job_420
Job_428 Job_399
Job_428 Job_419
Job_428 Job_346
Job_428 Job_334
Job_428 Job_397
Job_428 Job_396
Job_428 Job_438
Job_428 Job_337
Job_43 Job_22
Job_43 Job_35
Job_43 Job_36
Job_43 Job_37
Job_43 Job_34
Job_43 Job_33
Job_430 Job_294
Job_431
Job_432 Job_431
Job_433 Job_317
Job_433 Job_316
Job_433 Job_352
Job_433 Job_349
Job_435 Job_415 Sjob_VRNT_37
Job_436 Job_415 Sjob_VRNT_38
Job_437 Job_415 Sjob_VRNT_39
Job_438 Job_437
Job_439 Job_384
Job_44 Job_24
Job_440 Job_432
Job_440 Job_439
Job_441 Job_317
Job_442
Job_444 Job_5 Sjob_51
Job_445 Job_37
Job_445 Job_444
Job_446 Job_37
Job_446 Job_171
Job_446 Job_174
Job_447 Job_446
Job_448 Job_37
Job_448 Job_171
Job_448 Job_174
Job_449 Job_448
Job_45 Job_23
Job_45 Job_30
Job_45 Job_37
Job_45 Job_34
Job_45 Job_33
Job_45 Job_35
Job_45 Job_36
Job_450 Job_433
Job_450 Job_440
Job_450 Job_417
Job_450 Job_398
Job_450 Job_372
Job_450 Job_369
Job_450 Job_368
Job_450 Job_348
Job_450 Job_366
Job_450 Job_365
Job_450 Job_363
Job_450 Job_362
Job_450 Job_360
Job_450 Job_359
Job_450 Job_358
Job_450 Job_357
Job_450 Job_356
Job_450 Job_355
Job_450 Job_354
Job_450 Job_338
Job_450 Job_379
Job_450 Job_34
Job_450 Job_64
Job_451 Job_420 Sjob_SRVN_2
Job_452
Job_453 Job_452
Job_454 Job_453
Job_454 Job_43
Job_454 Job_74
Job_454 Job_48
Job_454 Job_467
Job_455 Job_41
Job_455 Job_459
Job_456 Job_457
Job_456 Job_458
Job_456 Job_459
Job_456 Job_455
Job_457 Job_45
Job_457 Job_74
Job_458 Job_40
Job_458 Job_32
Job_459 Job_457
Job_459 Job_458
Job_459 Job_31
Job_459 Job_36
Job_459 Job_33
Job_459 Job_30
Job_46 Job_25
Job_46 Job_30
Job_46 Job_37
Job_460 Job_16
Job_461 Job_16
Job_462 Job_50
Job_463 Job_50
Job_464 Job_50
Job_465 Job_551
Job_467 Job_453
Job_468
Job_469 Job_468
Job_47 Job_21
Job_47 Job_37
Job_470 Job_469
Job_471 Job_469
Job_472 Job_470
Job_472 Job_471
Job_473 Job_472
Job_474
Job_475 Job_474
Job_476 Job_475
Job_477 Job_476
Job_478 Job_477
Job_479 Job_478
Job_48 Job_7
Job_48 Job_37
Job_48 Job_34
Job_480 Job_215
Job_481 Job_480
Job_482 Job_481
Job_482 Job_214
Job_482 Job_213
Job_483 Job_481
Job_483 Job_215
Job_484 Job_45
Job_484 Job_74
Job_484 Job_482
Job_484 Job_483
Job_485
Job_486 Job_485
Job_487 Job_486
Job_488 Job_576
Job_489 Job_488
Job_49 Job_551 BDP2_Sjob_24
Job_490 Job_579
Job_490 Job_43
Job_491 Job_577
Job_492 Job_488
Job_493 Job_492
Job_494 Job_416 Sjob_UWF_2
Job_495 Job_416 Sjob_UWF_3
Job_496 Job_416 Sjob_UWF_4
Job_497 Job_496
Job_497 Job_495
Job_497 Job_494
Job_498 Job_488
Job_499
Job_5
Job_50 Job_551 BDP2_Sjob_26
Job_500 Job_488
Job_501 Job_170
Job_501 Job_110
Job_501 Job_44
Job_501 Job_45
Job_501 Job_68
Job_501 Job_74
Job_501 Job_75
Job_501 Job_48
Job_501 Job_43
Job_501 Job_42
Job_501 Job_40
Job_501 Job_189
Job_501 Job_81
Job_501 Job_71
Job_502 Job_551 BDP2_Sjob_54
Job_503 Job_502
Job_503 Job_34
Job_503 Job_37
Job_504 Job_446
Job_505 Job_551 BDP2_Sjob_55
Job_506 Job_551 BDP2_Sjob_56
Job_507 Job_505
Job_507 Job_506
Job_507 Job_37
Job_508 Job_551 BDP2_Sjob_57
Job_509 Job_508
Job_509 Job_37
Job_51 Job_5 Sjob_25
Job_510 Job_30
Job_510 Job_32
Job_510 Job_34
Job_510 Job_36
Job_510 Job_40
Job_510 Job_41
Job_510 Job_42
Job_510 Job_43
Job_510 Job_45
Job_510 Job_56
Job_510 Job_489
Job_510 Job_490
Job_510 Job_491
Job_510 Job_531
Job_510 Job_532
Job_510 Job_556
Job_510 Job_557
Job_510 Job_558
Job_510 Job_559
Job_510 Job_560
Job_510 Job_561
Job_510 Job_562
Job_510 Job_563
Job_510 Job_564
Job_510 Job_565
Job_510 Job_566
Job_510 Job_567
Job_510 Job_568
Job_510 Job_569
Job_510 Job_570
Job_510 Job_571
Job_511 Job_56
Job_511 Job_515
Job_511 Job_514
Job_511 Job_513
Job_511 Job_112
Job_512 Job_511
Job_513 Job_56
Job_513 Job_74
Job_513 Job_33
Job_513 Job_36
Job_513 Job_34
Job_513 Job_64
Job_514 Job_509
Job_515 Job_507
Job_52 Job_551 BDP2_Sjob_29
Job_525 Job_513
Job_525 Job_43
Job_526 Job_525
Job_527 Job_212
Job_528 Job_56
Job_528 Job_71
Job_528 Job_111
Job_528 Job_112
Job_528 Job_461
Job_528 Job_488
Job_528 Job_515
Job_528 Job_514
Job_528 Job_35
Job_528 Job_36
Job_528 Job_37
Job_528 Job_74
Job_528 Job_132
Job_528 Job_460
Job_528 Job_513
Job_529
Job_53 Job_551 BDP2_Sjob_30
Job_530 Job_529
Job_531 Job_578
Job_531 Job_488
Job_532 Job_488
Job_533
Job_534 Job_533
Job_535 Job_534
Job_536 Job_453
Job_537 Job_453
Job_538 Job_453
Job_539 Job_454
Job_539 Job_467
Job_539 Job_536
Job_539 Job_537
Job_539 Job_538
Job_54 Job_5 Sjob_27
Job_540 Job_530
Job_541
Job_542
Job_543 Job_542
Job_544
Job_545 Job_544
Job_546 Job_383
Job_546 Job_352
Job_546 Job_545
Job_547
Job_548 Job_74
Job_548 Job_45
Job_549 Job_215
Job_549 Job_551 BDP2_Sjob_58
Job_55 Job_5 Sjob_28
Job_550 Job_398
Job_550 Job_391
Job_550 Job_488
Job_551
Job_552 Job_171
Job_553 Job_551 BDP2_Sjob_61
Job_554 Job_551 BDP2_Sjob_62
Job_555 Job_551 BDP2_Sjob_63
Job_556 Job_488
Job_556 Job_32
Job_556 Job_40
Job_556 Job_41
Job_557 Job_488
Job_557 Job_34
Job_557 Job_36
Job_557 Job_42
Job_558 Job_488
Job_558 Job_45
Job_558 Job_36
Job_559 Job_488
Job_559 Job_580
Job_56 Job_16
Job_560 Job_488
Job_560 Job_503
Job_561 Job_488
Job_562 Job_488
Job_563 Job_488
Job_564 Job_563
Job_565 Job_45
Job_565 Job_43
Job_565 Job_491
Job_565 Job_490
Job_566 Job_488
Job_566 Job_565
Job_567 Job_488
Job_567 Job_565
Job_568 Job_488
Job_568 Job_565
Job_569 Job_488
Job_569 Job_565
Job_57 JOb_43
Job_57 Job_45
Job_570 Job_488
Job_570 Job_491
Job_570 Job_45
Job_571 Job_488
Job_572
Job_573 Job_143
Job_574 Job_551 BDP2_Sjob_65
Job_575 Job_117
Job_576 Job_551 BDP2_Sjob_70
Job_577 Job_551 BDP2_Sjob_71
Job_578 Job_551 BDP2_Sjob_72
Job_579 Job_551 BDP2_Sjob_73
Job_58 Job_48
Job_580 Job_551 BDP2_Sjob_74
Job_59 Job_42
Job_60 Job_56
Job_60 Job_57
Job_60 Job_58
Job_60 Job_59
Job_60 Job_44
Job_61 Job_57
Job_61 Job_58
Job_61 Job_59
Job_61 Job_60
Job_62 Job_61
Job_63 Job_62
Job_64 Job_49
Job_65 Job_51
Job_66 Job_50
Job_67 Job_50
Job_68 Job_30
Job_68 Job_37
Job_68 Job_34
Job_68 Job_33
Job_68 Job_35
Job_68 Job_36
Job_68 Job_464
Job_68 Job_52
Job_68 Job_74
Job_69 Job_54
Job_69 Job_36
Job_69 Job_33
Job_69 Job_464
Job_7 Job_551 BDP2_Sjob_22
Job_70 Job_55
Job_70 Job_36
Job_70 Job_33
Job_70 Job_464
Job_71 Job_53
Job_71 Job_36
Job_71 Job_37
Job_72 Job_551 BDP2_Sjob_32
Job_73 Job_551 BDP2_Sjob_31
Job_74 Job_30
Job_74 Job_34
Job_74 Job_33
Job_74 Job_36
Job_74 Job_35
Job_74 Job_37
Job_74 Job_72
Job_74 Job_45
Job_75 Job_30
Job_75 Job_34
Job_75 Job_33
Job_75 Job_464
Job_75 Job_36
Job_75 Job_35
Job_75 Job_37
Job_75 Job_73
Job_75 Job_68
Job_76 Job_551 BDP2_Sjob_34
Job_77 Job_76
Job_78 Job_5 Sjob_33
Job_79 Job_78
Job_8 Job_551 BDP2_Sjob_1
Job_80 Job_551 BDP2_Sjob_36
Job_81 Job_33
Job_81 Job_34
Job_81 Job_30
Job_81 Job_37
Job_81 Job_80
Job_82 Job_551 BDP2_Sjob_35
Job_83 Job_82
Job_84 Job_5 Sjob_41
Job_85 Job_84
Job_85 Job_36
Job_86 Job_74
Job_86 JOb_43
Job_86 Job_36
Job_87 Job_43
Job_88 Job_86
Job_89 Job_104
Job_89 Job_105
Job_89 Job_36
Job_89 Job_66
Job_89 Job_68
Job_89 Job_96
Job_89 Job_462
Job_9 Job_551 BDP2_Sjob_5
Job_90 Job_86
Job_91 Job_86
Job_91 Job_90
Job_92 Job_103
Job_92 Job_89
Job_93 Job_5 Sjob_42
Job_94 Job_5 Sjob_43
Job_95 Job_93
Job_96 Job_462
Job_96 Job_94
Job_97 Job_98
Job_98
Job_99
Job_Tealium_AS_01_P
Job_Tealium_ES_01_P
Job_581 Job_228
Job_581 Job_230
Job_581 Job_231
Job_581 Job_232
Job_581 Job_233
Job_581 Job_235
Job_581 Job_164
Job_582 Job_398
Job_582 Job_391
Job_582 Job_396
Job_582 Job_488
Job_583 Job_571
Job_583 Job_556
Job_583 Job_560
Job_583 Job_557
Job_583 Job_558
Job_583 Job_568
Job_583 Job_561
Job_583 Job_567
Job_583 Job_566
Job_583 Job_559
Job_583 Job_570
Job_583 Job_569
Job_583 Job_564
Job_583 Job_488
Job_583 Job_56
Job_583 Job_34
Job_583 Job_45
Job_583 Job_43
Job_583 Job_30
Job_583 Job_503
Job_583 Job_42
Job_583 Job_531
Job_583 Job_40
Job_583 Job_491
Job_583 Job_490
Job_583 Job_36
Job_570 Job_74
Job_565 Job_74
Job_565 Job_56
Job_TEST_AP
Job_TEST_AF
Job_320 Job_317
Job_584 Job_551 BDP2_Sjob_75
Job_585 Job_551 BDP2_Sjob_76
Your attempt recursively adds rows based on DEPENDANT_JOB_NO and recursively adds rows based on DEPENDANT_SUB_JOB_NO, but that isn't finding dependent sub jobs used by dependent jobs or dependent jobs used by dependent sub jobs. To do this, use a single recursive cte with two unions:
with recursive rec_cte as (
select JOB_NO,DEPENDANT_JOB_NO,DEPENDANT_SUB_JOB_NO
from CTL_TALEND_JOBS_METADATA
where JOB_NO ='Job_198'
union all
select p.DEPENDANT_JOB_NO,h.DEPENDANT_JOB_NO,h.DEPENDANT_SUB_JOB_NO
from rec_cte p
inner join CTL_TALEND_JOBS_METADATA h
on (h.JOB_NO = p.DEPENDANT_JOB_NO)
union all
select p.DEPENDANT_SUB_JOB_NO,h.DEPENDANT_JOB_NO,h.DEPENDANT_SUB_JOB_NO
from rec_cte p
inner join CTL_TALEND_JOBS_METADATA h
on (h.JOB_NO = REPLACE(p.DEPENDANT_SUB_JOB_NO,'BDP2_Sj','J'))
)
select * from rec_cte