Need to add a field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to add a field

Author

Topic

javahf
Starting Member

34 Posts

Posted - 2011-04-06 : 23:20:05

I have a inquiry routine that got lots of help here : it is
SELECT o.Orderid,o.OrderStatus,o.BillingLastName,o.BillingCity,o.BillingState,o.PaymentAmount,o.TotalShippingCost, o.SalesTax1 ,

STUFF((SELECT ',' + CAST(d.Options AS VARCHAR(100)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'')
AS Options
FROM Orders o
where o.OrderID > 1179[

I need to add another field d.productname just before options in my report . but if I add it at the top part of select I get the message :
The multi-part identifier "d.ProductName" could not be bound
If I add it at the end the program loops -

Can you assist ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-06 : 23:42:17

i assumed ProductName is in the OrderDetails table. So for one order there might be more than one ProductName. You can't just include the column there without INNER JOIN to the table.

If you want the productname to be in CSV like the Options, use for xml path method as what you have done for the options.

KH
[spoiler]Time is always against us[/spoiler]

javahf
Starting Member

34 Posts

Posted - 2011-04-06 : 23:46:23

Yes Prodcutname is in orderdetails . But i am novice and did not fully get your anser - can you please do it in the code .

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-06 : 23:56:15

[code]
SELECT o.Orderid,o.OrderStatus,o.BillingLastName,o.BillingCity,o.BillingState,o.PaymentAmount,o.TotalShippingCost, o.SalesTax1 ,
STUFF((SELECT ',' + ProductName FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') as ProductName,
STUFF((SELECT ',' + CAST(d.Options AS VARCHAR(100)) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'')
AS Options
FROM Orders o
where o.OrderID > 1179
[/code]

KH
[spoiler]Time is always against us[/spoiler]

javahf
Starting Member

34 Posts

Posted - 2011-04-07 : 00:02:18

Perfect - thanks - worked like charm !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources