January 30, 2009 at 6:16 am
I don't understand why I get Ambiguous column name when using a union and order by statement. I thought the order by was supposed to use the column names. I know the column name NAME is available in both tables and I can even give an alias to the column but shouldn't the order by, especially in a union only use the column aliases?
SELECT obj.NAME, col.NAME ColName
FROM sysobjects obj
INNER JOIN syscolumns col ON obj.id = col.id
WHERE obj.NAME LIKE 'sys%'
UNION ALL
SELECT obj2.NAME, col.NAME ColName
FROM sysobjects obj2
INNER JOIN syscolumns col ON obj2.id = col.id
WHERE obj2.NAME NOT LIKE 'sys%'
ORDER BY NAME
I'm having a problem with our tables and columns but instead of creating our structure I figured I'd use what everyone has. My query is actually using two totally different tables. Now I can change it to
SELECT obj.NAME, col.NAME ColName
FROM sysobjects obj
INNER JOIN syscolumns col ON obj.id = col.id
WHERE obj.NAME LIKE 'sys%'
UNION ALL
SELECT obj2.NAME, col.NAME ColName
FROM sysobjects obj2
INNER JOIN syscolumns col ON obj2.id = col.id
WHERE obj2.NAME NOT LIKE 'sys%'
ORDER BY obj.NAME
BUT why does obj. have to be there? In the second union it's obj2 so it's really not sorting by obj.name but the column NAME. This doesn't occur in 2005 and this is mainly a backwards compatibility issue that I came across.
January 30, 2009 at 6:29 am
Keith DuAime (1/30/2009)
I don't understand why I get Ambiguous column name when using a union and order by statement. I thought the order by was supposed to use the column names. I know the column name NAME is available in both tables and I can even give an alias to the column but shouldn't the order by, especially in a union only use the column aliases?
SELECT obj.NAME, col.NAME ColName
FROM sysobjects obj
INNER JOIN syscolumns col ON obj.id = col.id
WHERE obj.NAME LIKE 'sys%'
UNION ALL
SELECT obj2.NAME, col.NAME ColName
FROM sysobjects obj2
INNER JOIN syscolumns col ON obj2.id = col.id
WHERE obj2.NAME NOT LIKE 'sys%'
ORDER BY NAME
I'm having a problem with our tables and columns but instead of creating our structure I figured I'd use what everyone has. My query is actually using two totally different tables. Now I can change it to
SELECT obj.NAME, col.NAME ColName
FROM sysobjects obj
INNER JOIN syscolumns col ON obj.id = col.id
WHERE obj.NAME LIKE 'sys%'
UNION ALL
SELECT obj2.NAME, col.NAME ColName
FROM sysobjects obj2
INNER JOIN syscolumns col ON obj2.id = col.id
WHERE obj2.NAME NOT LIKE 'sys%'
ORDER BY obj.NAME
BUT why does obj. have to be there? In the second union it's obj2 so it's really not sorting by obj.name but the column NAME. This doesn't occur in 2005 and this is mainly a backwards compatibility issue that I came across.
Keith,
I guess You are right. It works fine in SQL Server 2005.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply