I have a look up table with this lookup override
SELECT RAW_SPIRIT_HTL_CXL_POL.TXT as TXT, MAX(RAW_SPIRIT_HTL_CXL_POL.BEG_DT) as BEG_DT, RAW_SPIRIT_HTL_CXL_POL.HTL_KEY as HTL_KEY, RAW_SPIRIT_HTL_CXL_POL.RP_KEY as RP_KEY
RAW_SPIRIT_HTL_CXL_POL INNER JOIN RAW_SPIRIT_BOOK_SEGMENT
RAW_SPIRIT_HTL_CXL_POL.HTL_KEY = RAW_SPIRIT_BOOK_SEGMENT.HTL_KEY
and RAW_SPIRIT_HTL_CXL_POL.RP_KEY = RAW_SPIRIT_BOOK_SEGMENT.RP_KEY
and RAW_SPIRIT_BOOK_SEGMENT.DOA > (select LAST_UPDATE_DATE from CONTROL
where PROCESS_NAME = 'm_edw_reservation_reminder_email_extract')
RAW_SPIRIT_HTL_CXL_POL.HTL_KEY, RAW_SPIRIT_HTL_CXL_POL.RP_KEY ----
and i'm getting this message in session log
And is taking lot of time for the session to finish up.
Message Code: TT_11195
Message: Warning: Unsorted input found when building the cache for the Lookup transformation [LKP_RAW_SPIRIT_HTL_CXL_POL]. The current number of entries in the index cache is 1. For optimal performance, use sorted input.
Try by removing the "---" at the end of the query.
Data being correct or not would depend on whether you get multiple matches and the policy configured for same. If the field_1 is the output col and you have configured the policy for returning first match row, you would get inconsistent result in both cases.Try configuring SQL override by sorting first on condition ports and then on output value.