I want to return a column value if column is available in table,if not, return a default value, then I face COLUMN_NAME ambiguous error when join two table SHAIN1 and RIREKI14. If select from only one table then query works ok but if I join two table, I face problem.
declare @sql nvarchar(max) = ' SELECT 1 as id, '+ (case when exists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='dbo' and TABLE_NAME='RIREKI14' and COLUMN_NAME='KOM001') then 'KOM001' else 'NULL' end) + ' as day ' + ' From RIREKI14 join SHAIN1 on RIREKI14.INCODE = SHAIN1.INCODE '; exec sp_executesql @sql
Help me please!
This is your logic:
declare @sql nvarchar(max) = '
SELECT 1 as id, '+
(case when exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'RIREKI14' and COLUMN_NAME = 'KOM001')
then 'KOM001'
else 'NULL'
end) + ' as day ' + '
From RIREKI14 join
SHAIN1
on RIREKI14.INCODE = SHAIN1.INCODE
';
exec sp_executesql @sql;
The only possibility for an ambiguous column name is the name coming from the case
. So, let's qualify it:
declare @sql nvarchar(max) = '
SELECT 1 as id, '+
(case when exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'RIREKI14' and COLUMN_NAME = 'KOM001')
then 'r.KOM001'
else 'NULL'
end) + ' as day ' + '
From RIREKI14 r join
SHAIN1 s
on r.INCODE = s.INCODE
';
exec sp_executesql @sql;