SOT: Correct way to match a record with a list in one field?

2003-07-01 Thread Lee Fuller
Ok guys.. Thinking caps: ON Have an nvarchar field in the database with data like: 1,3,9,12,2,51 Have a formfield with single number (not digit) data, like: 1 or 12 -- single whole numbers, maybe more than 1 digit. Now, would like to do something like this: SELECT EmailAddr, Recipie

Re: SOT: Correct way to match a record with a list in one field?

2003-07-01 Thread jon hall
Normalize your SubjectID column...otherwise you will need to either get creative with the string functions in sql, or do the compare with CF. -- jon mailto:[EMAIL PROTECTED] Tuesday, July 1, 2003, 2:58:55 PM, you wrote: LF> Ok guys.. Thinking caps: ON LF> Have an nvarchar field in the databas

Re: SOT: Correct way to match a record with a list in one field?

2003-07-01 Thread S . Isaac Dealey
It doesn't have to be _horribly_ creative... As long as you only need to find one element in the list, you should be able to use this: SELECT EmailAddr, RecipientID FROMSupportEmailRecipient WHERE SubjectID LIKE '%,#FORM.SubjectID#,%' I'm not storing comma delimited lists in the d

Re: SOT: Correct way to match a record with a list in one field?

2003-07-02 Thread Calvin Ward
PROTECTED]> Sent: Tuesday, July 01, 2003 6:47 PM Subject: Re: SOT: Correct way to match a record with a list in one field? > It doesn't have to be _horribly_ creative... As long as you only need to find one element in the list, you should be able to use this: > > SELE

RE: SOT: Correct way to match a record with a list in one field?

2003-07-02 Thread Philip Arnold
> Well, the only problem is that won't match the first and last > elements in that field, nor if it is the element in that > field, so you really need: > > SELECT EmailAddr, RecipientID > FROMSupportEmailRecipient > > WHERE > (SubjectID LIKE '%#FORM.SubjectID#,%' > OR > SubjectID LIKE

RE: SOT: Correct way to match a record with a list in one field?

2003-07-02 Thread DURETTE, STEVEN J (AIT)
ALUES FROM dbo.listToTable('#FORM.SubjectID#')) If you want the functions, let me know. Steve -Original Message- From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 6:20 AM To: CF-Talk Subject: RE: SOT: Correct way to match

RE: SOT: Correct way to match a record with a list in one field?

2003-07-02 Thread Philip Arnold
> SELECT EmailAddr, RecipientID > FROM SupportEmailRecipient > WHERE SubjectID IN (SELECT VALUES > FROM > dbo.listToTable('#FORM.SubjectID#')) > > If you want the functions, let me know. Shouldn't that be WHERE #form.SubjectID# IN (dbo.listToTable(SubjectID)) As the

RE: SOT: Correct way to match a record with a list in one field?

2003-07-02 Thread DURETTE, STEVEN J (AIT)
2003 8:02 AM To: CF-Talk Subject: RE: SOT: Correct way to match a record with a list in one field? > SELECT EmailAddr, RecipientID > FROM SupportEmailRecipient > WHERE SubjectID IN (SELECT VALUES > FROM > dbo.listToTable('#FORM.SubjectID#')) &g

Re: SOT: Correct way to match a record with a list in one field?

2003-07-02 Thread S . Isaac Dealey
gt; > Or did I miss something obvious? > - Calvin > - Original Message ----- > From: "S. Isaac Dealey" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Tuesday, July 01, 2003 6:47 PM > Subject: Re: SOT: Correct way t