Customizing BTO - SQL query help - need SKUs for stringProducts

Print Page | Close Window

SQL query help - need SKUs for stringProducts

Printed From: ProductCart E-Commerce Solutions
Category: ProductCart Build To Order
Forum Name: Customizing BTO
Forum Description: Interact with other users, resellers and developers about customizing BTO
URL: https://forum.productcart.com/forum_posts.asp?TID=4403
Printed Date: 21-November-2024 at 12:44pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com

Topic: SQL query help - need SKUs for stringProducts

Posted By: AndreaPou
Subject: SQL query help - need SKUs for stringProducts
Date 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

Replies:

Posted By: cognecy
Date 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

Posted By: AndreaPou
Date 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