Solved self join in mapping

I want to implement below flag condition in mapping

case
when id in (select distinct master_id from emp) then 'Y'
else 'N'
end

1: emp is flat file.
2: id and master_id both fields are coming from one table i.e. emp.

If I write

case
when emp.id in (select distinct emp.master_id from emp) then 'Y'
else 'N'
end

It gives me error that emp object not found.
Please help me write above condition in mapping.

Please let me if any details required.

Regards,
Prashant

Hi,

For making transformations, sub-requests, or any SQL manipulation on the file data, we suggest to first load the file in a database, through a stage for instance.
You'll then be able to use the SQL language, functions, and possibilities on your data.


Can you give more details on the structure and goal of your Mapping?

1. When do you want the "case" statement to be performed?
-> when writing to a file?
-> when reading a file?
-> As a join with a table?

2. If possible can you provide a screenshot of your Mapping?

Thanks

Hi,

1. When do you want the "case" statement to be performed?
-> when writing to a file?

2. Please find attachment.

Let me know if any other details required.

Regards,
Prashant

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?