Translating LPL code into SQL

patrick-domond

Trying to replicate LPL code of a JobRequisition step history record for a report of all JobRequisitions.

The history of the record comes from the JobRequisitionWorkflowStepHistory List

I am not sure how to replicate ( translate ) the filter above from the list to SQL referencing tables below:

JOBREQUISITIONWORKFLOWSTEP

JOBAPPLICATION

CANDIDATE

JOBREQUISITION

ATSWORKFLOWSTEP

Will appreciate any guidance,

Thanks

SQL CODE:

select distinct req.RECRUITER, candidate.CANDIDATE, ats1.DESCRIPTION from LMGHRDB.lm_ghr.JOBREQUISITION req
left join LMGHRDB.lm_ghr.JOBAPPLICATION jobapp
on jobapp.JOBREQUISITION = req.JOBREQUISITION
and jobapp.USACTOR = req.USACTOR
and jobapp.DELETEFLAG = 0

left join LMGHRDB.lm_ghr.CANDIDATE candidate
on candidate.CANDIDATE = jobapp.CANDIDATE
and candidate.USACTOR = req.USACTOR
and candidate.DELETEFLAG = 0

left join LMGHRDB.lm_ghr.JOBREQUISITIONWORKFLOWSTEP jreq
on jreq.JOBREQUISITION = req.JOBREQUISITION
and jreq.USACTOR = req.USACTOR
and jreq.DELETEFLAG= 0

left join LMGHRDB.lm_ghr.ATSWORKFLOWSTEP ats1
on ats1.UNIQUEID = req.UNIQUEID
and ats1.ATSWORKFLOW = 'NVH DEFAULT'

where req.JOBREQUISITION = 24103
and req.DELETEFLAG = 0

patrick-domond

Specifically this line

    Instance Selection

        where (JobRequisitionWorkflowStep != old JobRequisitionWorkflowStep)