May 29, 2003 at 10:02 am
--Does anybody have an explanation why the regular select
--with a dynamic order by clause works
-- and the one with FOR XML EXPLICIT does not?
use northwind
declare @sort tinyint
set @sort=0
select o.orderid, companyname,
convert(varchar(10), orderdate, 101) as 'dateordered',
d.Unitprice*quantity as 'Amount',
productname
from orders o join [order details]d on o.orderid = d.orderid
join customers c on o.customerid = c.customerid
join products p on d.productid = p.productid
order by case
when @sort = 0 then companyname
when @sort=1 then productname
when @sort=2 then 'dateordered'
when @sort=3 THEN replicate(' ',20-len(cast((d.Unitprice*quantity) as varchar(20))))+cast((d.Unitprice*quantity) as varchar(20)) end
go
-- and the one with FOR XML EXPLICIT does not?
declare @sort tinyint
set @sort=0
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [ROOT!1],
NULL AS [orders!2!OrderID],
NULL AS [orders!2!CompanyName],
NULL AS [orders!2!orderdate],
NULL AS [orderDet!3!Amount],
NULL AS [orderDet!3!ProductName]
UNION ALL
SELECT 2,
1,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
NULL, NULL
from orders o join customers c on o.customerid = c.customerid
union all
SELECT 3,
2,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
d.Unitprice*quantity as 'Amount',
productname
from orders o join [order details]d on o.orderid = d.orderid
join customers c on o.customerid = c.customerid
join products p on d.productid = p.productid
order by case
when @sort = 0 then [orders!2!CompanyName]
when @sort=1 then [orderDet!3!ProductName]
when @sort=2 then [orders!2!orderdate]
when @sort=3 THEN [orderDet!3!Amount]
END FOR XML EXPLICIT
go
-- but fixed order by will
declare @sort tinyint
set @sort=0
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [ROOT!1],
NULL AS [orders!2!OrderID],
NULL AS [orders!2!CompanyName],
NULL AS [orders!2!orderdate],
NULL AS [orderDet!3!Amount],
NULL AS [orderDet!3!ProductName]
UNION ALL
SELECT 2,
1,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
NULL, NULL
from orders o join customers c on o.customerid = c.customerid
union all
SELECT 3,
2,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
d.Unitprice*quantity as 'Amount',
productname
from orders o join [order details]d on o.orderid = d.orderid
join customers c on o.customerid = c.customerid
join products p on d.productid = p.productid
order by [orders!2!CompanyName]
-- [orderDet!3!ProductName]
--[orders!2!orderdate]
-- [orderDet!3!Amount]
FOR XML EXPLICIT
June 2, 2003 at 8:00 am
This was removed by the editor as SPAM
June 6, 2003 at 4:13 pm
I have seen this problem before. The ORDER BY clause refrences actual columns or expressions built form such columns. In your regular SELECT statment you used expressions of actual columns in the ORDER by clause , while you used derived names in the FOR XML statement. Since all the columns columns use in the SQL are derived , I suggest that you build the SQL statment first and then execute it as shown below (BTW I modified your original SQL statement so that it would run on my case sensitive SQL Server instance).
declare @sort tinyint
declare @SQL varchar(2000)
set @sort=3
set @SQL = '
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [ROOT!1],
NULL AS [Orders!2!OrderID],
NULL AS [Orders!2!CompanyName],
NULL AS [Orders!2!OrderDate],
NULL AS [orderDet!3!Amount],
NULL AS [orderDet!3!ProductName]
UNION ALL
SELECT 2,
1,
NULL,
o.OrderID,
CompanyName,
convert(varchar(10), OrderDate, 101),
NULL, NULL
from Orders o join Customers c on o.CustomerID = c.CustomerID
union all
SELECT 3,
2,
NULL,
o.OrderID,
CompanyName,
convert(varchar(10), OrderDate, 101),
[Amount]= d.UnitPrice*Quantity ,
ProductName
from Orders o join [Order Details]d on o.OrderID = d.OrderID
join Customers c on o.CustomerID = c.CustomerID
join Products p on d.ProductID = p.ProductID
order by' + ' ' + case
when @sort = 0 then '[Orders!2!CompanyName]'
when @sort=1 then '[orderDet!3!ProductName]'
when @sort=2 then '[Orders!2!OrderDate]'
when @sort=3 THEN '[orderDet!3!Amount]'
END + ' ' + 'FOR XML EXPLICIT'
--print @SQL
exec(@SQL)
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply