What if you select the Sorted Input option in Table Comparison but forgot to give the order by condition?
I was encountered with a question today, “What will happen if I forgot to give the order by in Query Transform but selected Sorted Input option in Table Comparison?”
To this, I responded, “Job will get executed successfully, but it’ll give warning.”
Now was I right?
Let’s see this practically,
Below is the mapping I’ve created:
After executing the job from Designer result turned out to be,
Since I ran the job in debug mode so,
The output from Query to Table Comparison and from Table Comparison to Target is:
Why did it generate an ‘I’ OPCODE?
It’s because when Table comparison compares the first row (Target with CUST_ID: 100) with the incoming record, CUST_ID: 103, it finds that it isn’t present in the target so it sends an ‘I’ for it. But Sorted Input property compares the Target values with source values in ASCENDING order. Since 103 cannot be compared with 100, hence job fails and gives the desired error.
If the data coming from the source, in sorted order with CUST_ID:100 at the top, then the job would’ve been successfully executed and changed records had been updated.
Please correct me if my understanding is wrong!