syntax error on a memo 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

syntax error on a memo field

Author

Topic

javahf
Starting Member

34 Posts

Posted - 2011-04-25 : 01:50:37

I have the following inquiry :
SELECT o.Orderid,o.OrderStatus,o.BillingLastName,o.BillingState,o.PaymentAmount,o.TotalShippingCost, o.SalesTax1 ,
STUFF ((SELECT ',' + d.Productname + d.options FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') AS
Productname
FROM Orders o
where o.OrderID > 1179

which was working . But I added d.options field ( as you can see ) - d.option is a memo

d.productname is a text field ( and it was working )
now get an error
Your SQL is invalid: The data types varchar and text are incompatible in the add operator.
I think it is because d.options is a memo field - how do I correct it ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-25 : 01:59:57

What's the max size of the data in that column? If it's 8000 or less, you can use CONVERT to get it to a varchar. If you are using 2005+, then you can use varchar(max) where size won't matter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

javahf
Starting Member

34 Posts

Posted - 2011-04-25 : 02:04:07

quote:
Originally posted by tkizer

What's the max size of the data in that column? If it's 8000 or less, you can use CONVERT to get it to a varchar. If you are using 2005+, then you can use varchar(max) where size won't matter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

It is less than 8000 - so do I CONVERT before and use the output instead . Could you please illustrate it in the inquiry as I am new to SQL .
Regards

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-25 : 02:05:49

CONVERT(varchar(8000), d.options)

If it's less than 8000, then you should consider changing the data type as text is a pain to work with. And use varchar(max) for 8000+.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-25 : 02:08:58

convert d.option to varchar before concatenate


SELECT ',' + d.Productname + convert(varchar(8000), d.options) FROM Orderdetails d

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-25 : 02:09:56

self reminder : refresh ... refresh ...

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

youky
Starting Member

5 Posts

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

unspammed

javahf
Starting Member

34 Posts

Posted - 2011-04-25 : 02:35:28

quote:
Originally posted by tkizer

CONVERT(varchar(8000), d.options)

If it's less than 8000, then you should consider changing the data type as text is a pain to work with. And use varchar(max) for 8000+.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Now I have the inquiry as :
SELECT o.Orderid,o.OrderStatus,o.BillingLastName,o.BillingState,o.PaymentAmount,o.TotalShippingCost, o.SalesTax1 ,
STUFF ((SELECT ',' + d.Productname FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') AS
Productname ,
STUFF ((SELECT ',' + CONVERT(varchar(8000), d.options) FROM Orderdetails d WHERE o.Orderid = d.Orderid FOR XML PATH('')),1,1,'') AS
Options
FROM Orders o
where o.OrderID > 1179
which works but my data d.options has some data that I like to filter and not show here is an example - this is output for d.options
[Handlebars:Riser + Oury Grips][Size:59][Add Tire Set:No Extra Tire Set][Pedal Type:Standard Pedal Set][Add Hold Fast Straps:No Hold Fast Straps]

As you can see some options are NO ( that means customer did not select them ) -
so the ideal output should be :
Handlebars:Riser + Oury Grips][Size:59][Add Tire Set:No Extra Tire Set][Pedal Type:Standard Pedal Set][Add Hold Fast Straps:No Hold Fast Straps]

and not show tire set and hold fast straps since they were not chosen . so if within [ ]
there is :NO as a relue we do not want to show any data between the [ ] . So I need to scan the output for that - how do I do that ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-25 : 02:57:47

AS any other normal query filtering.

SELECT ',' + CONVERT(varchar(8000), d.options) FROM Orderdetails d WHERE o.Orderid = d.Orderid AND d.option NOT LIKE '%:NO%' FOR XML PATH('')

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources