Dean, I am not arguing how the list was stored for Paul's case. I provided a link and functionality to a helper function that helps massage lists from the perspective of TSQL and CF alike.
As for purely looking at Paul's case, you are right. In the future, I will endeavor to alleviate quick statements so that I can alleviate miscommunication for our more detailed members. Teddy ----- Original Message ----- From: Dean H. Saxe To: discussion@acfug.org Sent: Tuesday, June 05, 2007 1:49 PM Subject: Re: [ACFUG Discuss] OT: SQL help Sure it is, Teddy. 1NF requires atomic data. Data with formatting, such as a list, is not atomic and therefore not 1NF. http://databases.about.com/od/specificproducts/a/firstnormalform.htm -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] Here in America everything is bought and sold, you can get anything for little bits of gold. We'll rape the earth and ruin the air, cut down every tree from here to there. -- Donna The Buffalo "America" On Jun 5, 2007, at 1:50 PM, Teddy R Payne wrote: I do not see it as a violation of the 1NF, but more of a tool to turn a CF list into a query in SQL Server so that you can loop over a list in TSQL without using text searches. This enables you to have the ability to convert badly formed data into normalized data. Teddy ----- Original Message ----- From: Dean H. Saxe To: discussion@acfug.org Sent: Tuesday, June 05, 2007 1:33 PM 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 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 -------------------------------------------------------------
