MessagePaul, Here was my 2 minute approach: -- variables declare @order_ID int , @optionIDs varchar(2000)
-- constants select @orderID = 12387 -- get the list of numbers from the column SELECT @optionIDs = oi.optionIDs FROM dbo.ORDER_ITEMS oi LEFT OUTER JOIN dbo.AP_translation tr ON tr.option_id where oi.order_id = @order_ID -- Get a query of integers select convert(int,value) from dbo.split(@optionIDs,',') ----- Original Message ----- From: Paul Morton To: [email protected] Sent: Tuesday, June 05, 2007 2:15 PM Subject: RE: [ACFUG Discuss] OT: SQL help Teddy, Any idea why do I get an error in Query Analyzer : SQL Statement SELECT * FROM dbo.ORDER_ITEMS oi LEFT OUTER JOIN dbo.AP_translation tr ON tr.option_id IN (select convert(int,Value) from dbo.Split(oi.OptionIDs, ',')) WHERE (oi.Order_ID = 12387) Error received: Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near '.'. Paul -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Teddy R Payne Sent: Tuesday, June 05, 2007 12:10 PM To: [email protected] Subject: Re: [ACFUG Discuss] OT: SQL help You use it in an adhoc query in query analyzer or SSMA. I have not tried it as an ad hoc query in CF as I myself tend not to use cfquery wherever I can. Teddy ----- Original Message ----- From: Paul Morton To: [email protected] Sent: Tuesday, June 05, 2007 1:58 PM Subject: RE: [ACFUG Discuss] OT: SQL help Without creating a SP, can I use this in a select statement? -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Teddy R Payne Sent: Tuesday, June 05, 2007 11:45 AM To: [email protected] Subject: Re: [ACFUG Discuss] OT: SQL help Paul, There is already an open source function in SQL Server to do this for you: http://www.4guysfromrolla.com/webtech/031004-1.shtml They explain how to put it together. Here is the function creation for how I put it together from their explanation: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Split]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[Split] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE FUNCTION dbo.Split ( @List nvarchar(4000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(250) ) AS BEGIN While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue ( value ) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End Insert Into @RtnValue ( Value ) Select Value = ltrim(rtrim(@List)) Return END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO It is implemented as such: CREATE PROCEDURE getEmployeesByID(@employeeList nvarchar(1000)) AS Select employeeId, -- integer (int) FirstName, LastName from #myTable Where employeeId IN (Select convert(int,Value) from dbo.Split(@employeeList,',')) Cheers, Teddy ----- Original Message ----- From: Paul Morton To: [email protected] Sent: Tuesday, June 05, 2007 1:36 PM Subject: [ACFUG Discuss] OT: SQL help I apologize for the OT post, but I'm getting frustrated. I am attempting to use a UDF to split a varchar (comma delimited list of ints) in an IN clause in a SQL statement, and I'm not able to get the SQL statement to pass a Table field to the UDF. I'm hoping someone sees a glaring error, or can tell me I can't do this. MS SQL 2000 ________________________________________________________________ CREATE FUNCTION dbo.Split ( @List nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) Return END ________________________________________________________________________________ SQL Statement SELECT * FROM dbo.ORDER_ITEMS oi LEFT OUTER JOIN dbo.AP_translation tr ON tr.option_id IN (select convert(int,Value) from dbo.Split(oi.OptionIDs, ',')) WHERE (oi.Order_ID = 12387) _____________________________________________________________________________ Error received: Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near '.'. OptionIDs is a varchar(255) field with a comma delimited list of integers. It doesn't like the oi.OptionIDs. If I put in a list like '0,1,2' in it's place, the query works. Can I not pass a field into a UDF? Is there another way to skin this cat? Paul ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
