ProductCart E-Commerce Solutions Homepage
Forum Home Forum Home > ProductCart Build To Order > Customizing BTO
  New Posts New Posts RSS Feed - SQL query help - need SKUs for stringProducts
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

SQL query help - need SKUs for stringProducts

 Post Reply Post Reply
Author
Message
AndreaPou View Drop Down
Newbie
Newbie


Joined: 19-November-2010
Status: Offline
Points: 0
Post Options Post Options   Thanks (0) Thanks(0)   Quote AndreaPou Quote  Post ReplyReply Direct Link To This Post Topic: SQL query help - need SKUs for stringProducts
    Posted: 06-April-2011 at 3:17pm
Hi Everyone,

I'm stuck on a SQL query. I'm trying to get the SKUs for some BTO Items stored in a string.

SELECT configSessions.stringProducts FROM 
(ProductsOrdered INNER JOIN configSessions ON ProductsOrdered.idconfigSession = configSessions.idconfigSession)
WHERE ProductsOrdered.idOrder=16


returns
137,139,140,142,143,144,145,146,147,

it's a string of NTEXT type.

I need to get the SKUs for each of those products and would like it to come back to me like this in a recordset:
idProduct  | SKU
137             BSP1BMH001
139             BSP1BMH003
140             BSP1BMH004
142             BSP1BMH006
143             BSP1BMH007
144             BSP1BMH008
145             BSP1BMH009
146             BSP1BMH010
147             BSP1BMH011

I'm in SQL server on the server testing the statements before I even start the ASP code for it. I tried SQL - "IN" but it told me I had a clash with NTEXT & INT. I was going to try REPLACE and see if I could replace the commas with ',' but I think I'd still have the datatype clash. Right now, I'm trying to split the SQL string, but trying to find a code snippet that everyone agrees on is like searching for the word "the" on the internet.

Does anyone have any suggestions?

Thanks so much.

Best wishes,
Andrea





Edited by AndreaPou - 06-April-2011 at 3:17pm
Back to Top
cognecy View Drop Down
Certified ProductCart Developers
Certified ProductCart Developers
Avatar

Joined: 18-March-2006
Location: United States
Status: Offline
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote cognecy Quote  Post ReplyReply Direct Link To This Post Posted: 06-April-2011 at 4:12pm
NTEXT is a big problem since it is a text datatype.  You will not be able to search, sort or group an NTEXT column.
 
If you re at all familiar with SQL you can use your temp database to "CAST" that field as an nvarchar(MAX) and then pull the data from that field. 
 
I will send you my contact info in a PM ... give me a call and lets talk.
 
Mark Shipp
Cognecy Solutions, LLC
Hosting/Design/Customization/Database Migration
www.cognecy.com
www.cognecy.co.uk
Back to Top
AndreaPou View Drop Down
Newbie
Newbie


Joined: 19-November-2010
Status: Offline
Points: 0
Post Options Post Options   Thanks (0) Thanks(0)   Quote AndreaPou Quote  Post ReplyReply Direct Link To This Post Posted: 07-April-2011 at 2:29pm
Hi Mark,

Thanks for the CAST tip. I saw that in a lot of the split functions and troubleshooting tips for the datatype clash I was getting. So I read up on it. I did eventually figure it out just now. Wow. That was rough. I was seeing red errors for so long I almost didn't remember what output looked like. lol. I will keep your contact info though for the next time.

Thanks again.

Best wishes,
Andrea
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.04
Copyright ©2001-2021 Web Wiz Ltd.

This page was generated in 0.078 seconds.