not show some data in the report

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 Administration

not show some data in the report

Author

Topic

javahf
Starting Member

34 Posts

Posted - 2011-04-25 : 02:54:26

I have an inquiry :
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 ',' + replace( CAST(d.Options AS VARCHAR(200)), '</i>', '') FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'')
AS Options
FROM Orders o
where o.OrderID > 1179

The output of Options looks is a memo field and has the options was selected for a Bicycle and it looks like :
[Handlebars:Riser + Oury Grips][Size:55][Add Tire Set:No Extra Tire Set][Pedal Type:Standard Pedal Set][Add Hold Fast Straps:No Hold Fast Straps],

However if within a 2 brackets [ ] there is a :NO - that means user did not select that option and I do not want to print it . so I like the output to look like

[Handlebars:Riser + Oury Grips][Size:55][Pedal Type:Standard Pedal Set],

So both Tire set and Hold FAst options are gone -
How do I scan the data to get rid of any data between 2 [ ] that has a ":No" in there ?
That is the easiest rule i see that is common .
thanks for your help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-25 : 07:52:23

[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 ',' + replace( CAST(d.Options AS VARCHAR(200)), '</i>', '') FROM Orderdetails d WHERE o.Orderid = d.Orderid
and d.Options not like '%:NO%' FOR XML PATH('')),1,1,'')
AS Options
FROM Orders o
where o.OrderID > 1179[/code]

javahf
Starting Member

34 Posts

Posted - 2011-04-25 : 11:14:53

quote:

Originally posted by sunitabeck

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 ',' + replace( CAST(d.Options AS VARCHAR(200)), '</i>', '') FROM Orderdetails d WHERE o.Orderid = d.Orderid 
and d.Options not like '%:NO%' FOR XML PATH('')),1,1,'')
AS Options
FROM Orders o
where o.OrderID > 1179

Thank you ! that works like a charm

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-25 : 12:17:09

You are very welcome javahf, take care.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources