Trying to create a NOT EXISTS query in TCC for launching Transition Process

Summary

Trying to create a NOT EXISTS query in TCC for launching Transition Process

Content

HI All,

Can some one help me convert this SQL to a TCC script. I have tried a subquery which didn't work.

select * from Transition as t where not exists (select CandNumber from Transition as b where b.CandNumber = t.CandNumber and Process='Onboarding')

The Script should bring only the highlighted values.

CandNum     Process     Status

1111       PreHire         Completed 

1111       Onboarding Inprogress

2222       PreHire       Completed 

3333       PreHire Completed

 

The query i tried.
 

SubQuery

<quer:query projectedClass="TransitionProcess" alias="SQ" xmlns:quer="http://www.taleo.com/ws/integration/query"&gt;
  <quer:projections>