How To Join on Dynamic Column In Data Flow

Question

How To Join on Dynamic Column In Data Flow

Bikram Sahoo 0

My requirement is to join two generic data set and join them on parameterized column. I have done it but the inner join is not working and doing CROSS JOIN on given columns. Any help is aapreciated.

User's image

Anonymous

2023-10-05T12:18:49.43+00:00
@Bikram Sahoo

Thanks for Approaching MicrosoftQ&A

Check the join condition that you are using. Ensure that the join condition is correct and that it is joining on the correct columns. If the join condition is incorrect, you may need to modify it to join on the correct columns.

One possible solution is to use the ON clause to specify the conditions for the inner join. The ON clause should include the columns that are used to join the two data sets. For example, the following SQL query joins two tables on two columns:

SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2;

Check the join type that you are using. Ensure that you are using the correct join type for your scenario. If you are using an inner join, ensure that there are matching values in both data sets for the join condition. If there are no matching values, the inner join will not return any results.

Iyou can try using a Lookup transformation to join the data sets. The Lookup transformation allows you to join data sets on a parameterized column and provides more flexibility than the Join transformation.

if the issue still persists, kindly provide more details on the specific data sets and columns being used.
Bikram Sahoo 0 Reputation points

2023-10-05T13:36:31.4066667+00:00

Hi @Anonymous ,

Thank you for answering to my question but you can see the SS i am giving the correct column name in those parameters. I might be dumb because i am new to this. So my requirement is to make column name dynamic durin inner join.
Anonymous

2023-10-06T08:42:14.1633333+00:00
@Bikram Sahoo

To make column names dynamic during an inner join in SQL, one approach is to use dynamic SQL to add the inner join clause dynamically based on column names mentioned in another table**.** Another approach is to use a left join with both tables based on the condition that needs to be met**.** Here is an example of how to use dynamic SQL to add an inner join clause based on column names mentioned in another table:

DECLARE @column_name VARCHAR(50) SET @column_name = (SELECT column_name FROM table_name WHERE condition) DECLARE @sql NVARCHAR(MAX) SET @sql = 'SELECT * FROM table1 INNER JOIN table2 ON table1.' + @column_name + ' = table2.' + @column_name EXEC sp_executesql @sql

This code will add an inner join clause based on the column name mentioned in the table_name table that meets the specified condition. The @column_name variable is used to store the column name, and the @sql variable is used to store the SQL query with the inner join clause added dynamically. Finally, the sp_executesql system stored procedure is used to execute the dynamic SQL query.

for further queries please do let us know.
Anonymous

2023-10-10T04:23:18.43+00:00

@Bikram Sahoo

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Anonymous

2023-10-11T08:12:49.9433333+00:00

@Bikram Sahoo just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Bikram Sahoo 0 Reputation points

2023-10-11T10:00:46+00:00

Hi @Anonymous ,
I am just looking for to use dynamic parameter instead of inside sql query.
Anonymous

2023-10-12T11:49:06.0633333+00:00
@Bikram Sahoo

To use dynamic parameters instead of inside SQL query, you can use dynamic SQL in SQL Server. Dynamic SQL is a programming technique where you build SQL query as a string and execute it dynamically at runtime. It lets you build the general-purpose query on the fly using variables, based on the requirements of the application. Here are the steps to use dynamic parameters in dynamic SQL:

Declare a variable for the parameter.

Assign a value to the parameter variable.

Build the query string with the parameter using the + operator.

Execute the SQL query using the exec or sp_executesql command.

For example, the following is a dynamic SQL query that uses a parameter:

DECLARE @sql nvarchar(max) DECLARE @empId nvarchar(max) SET @empId = '5' SET @sql = 'SELECT * FROM EMPLOYEE WHERE EMPID =' + @empId EXEC sp_executesql @sql

Your answer

Anonymous

2023-10-05T12:18:49.43+00:00

@Bikram Sahoo

Thanks for Approaching MicrosoftQ&A

Check the join condition that you are using. Ensure that the join condition is correct and that it is joining on the correct columns. If the join condition is incorrect, you may need to modify it to join on the correct columns.

One possible solution is to use the ON clause to specify the conditions for the inner join. The ON clause should include the columns that are used to join the two data sets. For example, the following SQL query joins two tables on two columns:

SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2;

Check the join type that you are using. Ensure that you are using the correct join type for your scenario. If you are using an inner join, ensure that there are matching values in both data sets for the join condition. If there are no matching values, the inner join will not return any results.

Iyou can try using a Lookup transformation to join the data sets. The Lookup transformation allows you to join data sets on a parameterized column and provides more flexibility than the Join transformation.

if the issue still persists, kindly provide more details on the specific data sets and columns being used.
Bikram Sahoo 0 Reputation points

2023-10-05T13:36:31.4066667+00:00

Hi @Anonymous ,

Thank you for answering to my question but you can see the SS i am giving the correct column name in those parameters. I might be dumb because i am new to this. So my requirement is to make column name dynamic durin inner join.
Anonymous

2023-10-06T08:42:14.1633333+00:00

@Bikram Sahoo

To make column names dynamic during an inner join in SQL, one approach is to use dynamic SQL to add the inner join clause dynamically based on column names mentioned in another table**.** Another approach is to use a left join with both tables based on the condition that needs to be met**.** Here is an example of how to use dynamic SQL to add an inner join clause based on column names mentioned in another table:

DECLARE @column_name VARCHAR(50) SET @column_name = (SELECT column_name FROM table_name WHERE condition) DECLARE @sql NVARCHAR(MAX) SET @sql = 'SELECT * FROM table1 INNER JOIN table2 ON table1.' + @column_name + ' = table2.' + @column_name EXEC sp_executesql @sql

This code will add an inner join clause based on the column name mentioned in the table_name table that meets the specified condition. The @column_name variable is used to store the column name, and the @sql variable is used to store the SQL query with the inner join clause added dynamically. Finally, the sp_executesql system stored procedure is used to execute the dynamic SQL query.

for further queries please do let us know.
Anonymous

2023-10-10T04:23:18.43+00:00

@Bikram Sahoo

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Anonymous

2023-10-11T08:12:49.9433333+00:00

@Bikram Sahoo just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Bikram Sahoo 0 Reputation points

2023-10-11T10:00:46+00:00

Hi @Anonymous ,
I am just looking for to use dynamic parameter instead of inside sql query.
Anonymous

2023-10-12T11:49:06.0633333+00:00

@Bikram Sahoo

To use dynamic parameters instead of inside SQL query, you can use dynamic SQL in SQL Server. Dynamic SQL is a programming technique where you build SQL query as a string and execute it dynamically at runtime. It lets you build the general-purpose query on the fly using variables, based on the requirements of the application. Here are the steps to use dynamic parameters in dynamic SQL:

Declare a variable for the parameter.

Assign a value to the parameter variable.

Build the query string with the parameter using the + operator.

Execute the SQL query using the exec or sp_executesql command.

For example, the following is a dynamic SQL query that uses a parameter:

DECLARE @sql nvarchar(max) DECLARE @empId nvarchar(max) SET @empId = '5' SET @sql = 'SELECT * FROM EMPLOYEE WHERE EMPID =' + @empId EXEC sp_executesql @sql

Share via

How To Join on Dynamic Column In Data Flow

Your answer