DB is not my design. Trying to retrofit.
The split was an attempt to fix this code:
SELECT *
FROM dbo.ORDER_ITEMS oi LEFT OUTER JOIN
dbo.AP_translation tr ON tr.option_id IN
(oi.OptionIDs)
WHERE (oi.Order_ID = 12387)
Error received:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value
'534161004,534163025,0,0,0,0,0,0,0' to a column of data type int.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dean H. Saxe
Sent: Tuesday, June 05, 2007 11:33 AM
To: [email protected]
Subject: Re: [ACFUG Discuss] OT: SQL help
Even further off topic... why in the heck does a varchar have a comma
delimited list in it? That violates the first degree of normalization.
Also, why do you need to split it for an in clause? If the field data is
wrapped in parenthesis, shouldn't it work as is?
-dhs
Dean H. Saxe, CISSP, CEH
[EMAIL PROTECTED]
"What difference does it make to the dead, the orphans, and the homeless,
whether the mad destruction is wrought under the name of totalitarianism or
the holy name of liberty and democracy? "
--Gandhi
On Jun 5, 2007, at 1:36 PM, Paul Morton wrote:
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 <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 FusionLink <http://www.fusionlink.com>
-------------------------------------------------------------
-------------------------------------------------------------
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
-------------------------------------------------------------
