I have 3 tables which have a "sortOrder" column of int datatype. I have a
UNION query which is sorting on sortOrder column:

SELECT fgs.objectid, fgs.form, fgs.sortOrder, fgs.dbColName
FROM formgenselect fgs
WHERE fgs.form = 'personal'
UNION
SELECT sf.objectid, sf.form, sf.sortOrder, sf.dbColName
FROM security_filter sf
WHERE sf.form = 'personal'
ORDER BY sortOrder

This query works ok. Now in the first part of the above i do an outer join
with the third table:

SELECT fgs.objectid, fgs.form, fgs.sortOrder, fgs.dbColName
FROM formgenselect fgs LEFT JOIN formgen fg ON (fgs.form = fg.form) and
(fgs.dbColName = fg.dbColName)
WHERE fgs.form = 'personal'
UNION
SELECT sf.objectid, sf.form, sf.sortOrder, sf.dbColName
FROM security_filter sf
WHERE sf.form = 'personal'
ORDER BY sortOrder

Here query analyzer is giving me following error message:
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'sortOrder'.

Can somebody explain it to why it works in first case but not in the second
case. What is the solution?

Thanks

Here is the DDL:

CREATE TABLE [dbo].[FORMGENSELECT] (
[objectId] [T_IDENTIFIER] IDENTITY (1, 1) NOT NULL ,
[form] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sortOrder] [int] NULL ,
[dbColName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FORMGENSELECT] WITH NOCHECK ADD
CONSTRAINT [PK_FORMGENSELECT] PRIMARY KEY  NONCLUSTERED
(
  [objectId]
) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO


CREATE TABLE [dbo].[SECURITY_FILTER] (
[objectId] [T_IDENTIFIER] IDENTITY (1, 1) NOT NULL ,
[form] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sortOrder] [int] NULL ,
[dbColName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SECURITY_FILTER] WITH NOCHECK ADD
CONSTRAINT [PK_SECURITY_FILTER] PRIMARY KEY  NONCLUSTERED
(
  [objectId]
) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO


CREATE TABLE [dbo].[FORMGEN] (
[objectId] [T_IDENTIFIER] IDENTITY (1, 1) NOT NULL ,
[form] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sortOrder] [int] NULL ,
[dbColName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FORMGEN] WITH NOCHECK ADD
CONSTRAINT [PK_FORMGEN] PRIMARY KEY  NONCLUSTERED
(
  [objectId]
) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO


Show quoteHide quote

What happens if you use column aliases for sortOrder?

SELECT fgs.objectid, fgs.form, fgs.sortOrder AS SO, fgs.dbColName
FROM formgenselect fgs LEFT JOIN formgen fg ON (fgs.form = fg.form)
and
(fgs.dbColName = fg.dbColName)
WHERE fgs.form = 'personal'
UNION
SELECT sf.objectid, sf.form, sf.sortOrder AS SO, sf.dbColName
FROM security_filter sf
WHERE sf.form = 'personal'
ORDER BY SO

It worked. Any idea why it was not working before?

Show quoteHide quote

Because the SQL optimizer performs a UNION before it performs the ORDER
BY.  In the first scenario, you UNION the two sort order columns, so
the parser sees it as one (no confusion).  In the second scenario,
you've included a third table to the right of the original  UNION; this
adds a second possible sortorder column, which confused the optimizer.
To correct, try prefacing the ORDER BY clause with 'fgs' or using a
colum aliad as suggested by the previous poster.

Stu

S R H wrote:
Show quoteHide quote

Help! Users can't connect to database
Help with stored procedure using "IN"
Default constraints in SQL Server 2000
Bring back Query Analyser
Optimize the Query