Ambiguous column name when same column is in two tables

[ax3ld]

Hello,

I'm trying to export certain columns from a join, but since a column is in more than one table, I'm using table aliases.

EXEC sp_generate_merge 'actionXRole', @disable_constraints = 1, @delete_if_not_matched = 0, @nologo = 1, @include_use_db = 0, @schema = 'dbo', @debug_mode = 1, @ommit_generated_always_cols = 1, @cols_to_include = "'ActionXSecurityRoleid','asr.actionid','securityroleid'", @from ='from dbo.actionXRole asr inner join dbo.action a on a.actionid = asr.actionid where a.name = ''CharismaManualDisbursement'''

I've specified @cols_to_include with an alias for asr.actionid but it's excluded from the end result.
I've used @debug_mode to see what's happening and the debug made me realize that the alias is not properly placed.

SELECT ' ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 1 THEN ' ' ELSE ',' END + '('+ COALESCE('N''' + REPLACE(CONVERT(char(36),RTRIM([ActionXSecurityRoleid])),'''','''''')+'''','NULL')+',' + COALESCE('N''' + REPLACE(CONVERT(char(36),RTRIM([securityroleid])),'''','''''')+'''','NULL')+',' + COALESCE('N''' + REPLACE(CONVERT(char(36),RTRIM(asr.[actionid])),'''','''''')+'''','NULL') +')' from EbsMetadata.actionXSecurityRole asr inner join EbsMetadata.action a on a.actionid = asr.actionid where a.name = 'CharismaManualDisbursement'

With the alias like RTRIM(asr.[actionid])) the query works, but if I try to put it inside like RTRIM([asr.actionid])) it's not working.

It's there a parameter that can be used or this script it's not working with table aliases?

Thanks,
Alex

[dnlnln]

I'll have to look into that one. As a workaround, if you haven't tries this already, maybe try using a common table expression in the @from param so that it isn't necessary to use table aliases in the @cols_to_include param?

[ax3ld]

Hi,

I will give it a try. In the meantime, I did it with a subquery and it worked.

Thanks,
Alex