Ambiguos of the ORDER BY

Previously I am using mssql2000
My sql is like this
SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mycol
This query is running fine
Then recently I upgrade my mssql server to mssql2005
The query above return an error said "ambiguos"
Then I change my query to
SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mytable.mycol
It works.
I am confuse of why the query can work fine on mssql2000 but cant work
on mssql2005
And why it can work after I change the
ORDER BY mycol
To
ORDER BY mytable.mycol
'
Can someone explain? Thank you!brian...@.gmail.com wrote:
> Previously I am using mssql2000
> My sql is like this
> SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mycol
> This query is running fine
> Then recently I upgrade my mssql server to mssql2005
> The query above return an error said "ambiguos"
> Then I change my query to
> SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mytable.mycol
> It works.
> I am confuse of why the query can work fine on mssql2000 but cant work
> on mssql2005
> And why it can work after I change the
> ORDER BY mycol
> To
> ORDER BY mytable.mycol
> '
> Can someone explain? Thank you!
SQL allows duplicate column names in query results. Usually the
duplicate names come from two different tables that are joined so if
you reference such a column without specifying a qualifying alias the
result is ambiguous because you may in fact be referring to either of
two different columns.
In your case the columns with duplicate names are in fact the same
column so it doesn't really matter which you refer to but SQL Server
2005 (rightly in my view) still insists that you qualify the name with
an alias. In SQL 2000 the ORDER BY didn't generate this error message,
which caused a lot of problems and confusion when duplicate names were
used.
Arguably the designers of SQL are to blame for allowing duplicate
column names at all. You would be well advised to modify your query to
return unique column names. Otherwise how will you refer to this data
in your client app? Relying on column positional indexes isn't good
practice.
SELECT mycol AS mycol1,
mycol AS mycol2
FROM mytable
WHERE active=1
ORDER BY mycol1;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Alternative to cursor

Hello,
I would like to find and alternative to using a cursor and updating my
table with the calculated values. The following code snippet is from my
store procedure.
-- Calculate and Update LEWeightedTerm and LEWeightedRR
DECLARE LECursor CURSOR FOR
SELECT TransactionID, Term, BusinessUnitID, LEAlloc, CalcODRBefore
FROM stage_DealTransaction
WHERE COBDateID = @.COBDateID AND LEWeightedTerm is null AND
LEWeightedRR is null
OPEN LECursor
DECLARE @.tranID AS int
DECLARE @.term AS int
DECLARE @.LEAlloc AS float
DECLARE @.BUID AS varchar(15)
DECLARE @.ODR AS Varchar(2)
DECLARE @.LEWT AS float
DECLARE @.LEWRR AS float
DECLARE @.LEAllocSum AS float
DECLARE @.rate AS float
FETCH NEXT FROM LECursor
INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
While (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.LEAllocSum = 1
IF (@.BUID IS NOT NULL)
BEGIN
SELECT @.LEAllocSum = SUM(ISNULL(LEAlloc, 0)) FROM DealTransaction
WHERE BusinessUnitID = @.BUID AND COBDateID = @.COBDateID
END
IF (@.LEAllocSum = 0)
BEGIN
SET @.LEAllocSum = 1
END
IF (@.LEAlloc IS NULL)
BEGIN
SET @.LEAlloc = 0
END
SET @.LEWT = (@.LEAlloc * ISNULL(@.term, 0)) / @.LEAllocSum
SET @.rate = CONVERT(float, ISNULL(@.ODR, '41'))
SET @.LEWRR = (@.LEAlloc * @.rate) / @.LEAllocSum
UPDATE stage_DealTransaction SET LEWeightedTerm = @.LEWT ,
LEWeightedRR = @.LEWRR WHERE TransactionID = @.tranID AND COBDateID =
@.COBDateID
END
FETCH NEXT FROM LECursor INTO @.tranID, @.term, @.BUID, @.LEAlloc, @.ODR
END
CLOSE LECursor
DEALLOCATE LECursor
I would like to find a way to optimize it so that the updates can be
done in bulk rather than one record at a time.
Any feedback would be highly appreciated.
Thank you,
Zubinezubine@.gmail.com wrote:
> Hello,
> I would like to find and alternative to using a cursor and updating my
> table with the calculated values. The following code snippet is from
> my store procedure.
>
Could you summarize what you need this to do rather than making us analyze
the cursor code?
It always help to provide DDL and sample data as well. See
www.aspfaq.com/5006 for suggestions
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On 18 Nov 2005 07:04:41 -0800, zubine@.gmail.com wrote:

>Hello,
>I would like to find and alternative to using a cursor and updating my
>table with the calculated values. The following code snippet is from my
>store procedure.
Hi Zubine,
Try the following:
UPDATE s
SET LEWeightedTerm = COALESCE (s.LEAlloc * s.Term), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1),
LEWeightedRR = COALESCE (s.LEAlloc *
CAST(COALESCE(s.CalcODRBefore, '41') AS float)), 0)
/ COALESCE(NULLIF(dt.SumLEAlloc, 0), 1)
FROM stage_DealTransaction AS s
LEFT JOIN (SELECT BusinessUnitID,
SUM(LEAlloc) AS SumLEAlloc
FROM DealTransaction
WHERE dt.COBDateID = @.COBDateID
GROUP BY BusinessUnitID) AS dt
ON dt.BusinessUnitID = s.BusinessUnitID
WHERE s.COBDateID = @.COBDateID
AND s.LEWeightedTerm IS NULL
AND s.LEWeightedRR IS NULL
Depending on how your tables and data look, you might be able to replace
the LEFT JOIN with an INNER JOIN, and that might speed up the execution
as well.
Note that the stattement above is untested. See www.aspfaq.com/5006 if
you prefer a tested reply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)