Hi,
Thanks for the elements.
From what I see:
- Your source is an Excel File
- Your target here is a SQL Server table
As the Source is an Excel file, you'll not be able to make the select subrequest in the case / when statement directly on it like you did.
I see several possibilities to do it:
1. Load first the file in the table without the flag column, and then update it with the case / when just after.
As the data is in the database, you'll be able to perform the case / when and sub-request:
2. You could also go through a left join with the Sheet to search for the existence of the master_id.
This allows to do it without performing a subrequest:
Notes:
- I am using a stage in the middle just to show a different way, you could also use the 'self_join_emp_tbd'
- In this solution, you'll have to check the 'Use Distinct' option on the target Integration Template, as the Left Join can lead to multiple times the same line (For instance the '1' id is found three times in 'master_id'
- The case / when is different here as we are using the result of the left join that will produce a 'null' if the master_id is not found.
That's the solutions I see for the moment, but as usual Stambia is very agile and there are often multiples solutions to perform the same requirement, so if I think of another one I'll come back here to tell it.
Is this helping? Are these solutions corresponding to what you need to do?
