Exists join in select statement

Exists joins are required when we want to check for an existence of a records in an table without having duplicate records returned. I had this strange problem today where i was trying to fetch the records using an exists join and the results where not generating.

       

select generateOnly forceLiterals sum(Qty) from projEmplTrans
     where projEmplTrans.TransDate >= tsStartDate &&
	projEmplTrans.TransDate <= tsEndDate &&
	projEmplTrans.ProjId == projId
    exists join journalId from payrollJournalTable
	where payrollJournalTable.JournalId == conPeek( payrollJournalCon, i )
    join worker from payrollJournalLine
	where payrollJournalLine.Worker == projEmplTrans.Worker
	&& payrollJournalLine.PayrollJournal == payrollJournalTable.RecId;

 

The SQL Statement that is generated is

       

SELECT SUM(T1.QTY) FROM PROJEMPLTRANS T1 
WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'0001')) AND (((T1.TRANSDATE>={ts '2021-02-01 00:00:00.000'}) 
AND (T1.TRANSDATE<={ts '2021-02-28 00:00:00.000'})) AND (T1.PROJID=N'BAA'))) 

AND EXISTS 

(SELECT 'x' FROM SPYPAYROLLJOURNALLINE T2 CROSS JOIN SPYPAYROLLJOURNALTABLE T3 
WHERE (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'0001')) AND (T2.WORKER=0)) 
AND (((T3.PARTITION=5637144576) AND (T3.DATAAREAID=N'0001')) AND ((T2.PAYROLLJOURNAL=T3.RECID) AND (T3.JOURNALID=N'0001-00000181'))))

 

The problem here is that the T2.Worker == 0 which ideally should have been T2.Worker == T1.Worker. 

After a lot of trials realized that the Worker field in projEmplTrans is a deprecated field and hence the system was equating it to zero (0).