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

2003-07-02 Thread Calvin Ward 
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
cfif ListLen(Form.SubjectID)
WHERE
(SubjectID LIKE '%#FORM.SubjectID#,%'
OR
SubjectID LIKE '%,#FORM.SubjectID#,%'
OR
SubjectID LIKE '%,#FORM.SubjectID#%'
OR
SubjectID = '%#FORM.SubjectID#%')
/cfif

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 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:

 SELECT  EmailAddr, RecipientID
 FROMSupportEmailRecipient
 WHERE   SubjectID LIKE '%,#FORM.SubjectID#,%'

 I'm not storing comma delimited lists in the database, but rather, in
cases where I need or want an IN () statement that I couldn't otherwise
accomplish by passing a list to a stored procedure ( because I want to
continue using a stored procedure ) I'm passing the list and selecting WHERE
@mylist LIKE '%,' + cast(mycolumn as nvarchar) + ',%' which seems to work
fairly well...

 hth

  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 database with data like:
  1,3,9,12,2,51

  LF Have a formfield with single number (not digit) data,
  like:  1 or 12  --
  LF single whole numbers, maybe more than 1 digit.

  LF Now, would like to do something like this:

  LF SELECT  EmailAddr, RecipientID
  LF FROMSupportEmailRecipient
  LF WHERE   SubjectID IN ('#FORM.SubjectID#)

  LF It ain't workin... Need to see if the data from the
  formfield is IN the data
  LF in the table's record.

  ~~
  ~~~|
  Archives:
  http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
  Subscription: http://www.houseoffusion.com/cf_lists/index.
  cfm?method=subscribeforumid=4
  FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

  Signup for the Fusion Authority news alert and keep up
  with the latest news in ColdFusion and related topics.
  http://www.fusionauthority.com/signup.cfm

  Unsubscribe: http://www.houseoffusion.com/cf_lists/uns
  ubscribe.cfm?user=633.558.4




 s. isaac dealey972-490-6624

 new epoch  http://www.turnkey.to

 lead architect, tapestry cms   http://products.turnkey.to

 tapestry api is opensource http://www.turnkey.to/tapi

 certified advanced coldfusion 5 developer
 http://www.macromedia.com/v1/handlers/index.cfm?ID=21816


 
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

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
 cfif ListLen(Form.SubjectID)
 WHERE
 (SubjectID LIKE '%#FORM.SubjectID#,%'
 OR
 SubjectID LIKE '%,#FORM.SubjectID#,%'
 OR
 SubjectID LIKE '%,#FORM.SubjectID#%'
 OR
 SubjectID = '%#FORM.SubjectID#%')
 /cfif

 Or did I miss something obvious?

Actually, this is slightly better;

SELECT  EmailAddr, RecipientID
FROM SupportEmailRecipient
WHERE ','+SubjectID+',' LIKE '%,#FORM.SubjectID#,%'

I'm using + as I'm used to SQL Server, but for Access you'd use 




~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

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

2003-07-02 Thread DURETTE, STEVEN J (AIT) 
Sorry to chime in late.

I'm not sure what database you are using, but if it is MS SQL 2000, I
released a group of list related functions to anyone on the list who wanted
them.

Using them you could do:

SELECT EmailAddr, RecipientID
FROM SupportEmailRecipient
WHERE SubjectID IN (SELECT VALUES
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 a record with a list in one
field?


 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
 cfif ListLen(Form.SubjectID)
 WHERE
 (SubjectID LIKE '%#FORM.SubjectID#,%'
 OR
 SubjectID LIKE '%,#FORM.SubjectID#,%'
 OR
 SubjectID LIKE '%,#FORM.SubjectID#%'
 OR
 SubjectID = '%#FORM.SubjectID#%')
 /cfif

 Or did I miss something obvious?

Actually, this is slightly better;

SELECT  EmailAddr, RecipientID
FROM SupportEmailRecipient
WHERE ','+SubjectID+',' LIKE '%,#FORM.SubjectID#,%'

I'm using + as I'm used to SQL Server, but for Access you'd use 





~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
http://www.cfhosting.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

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 there's one entry in form.SubjectID and SubjectID in the table is a
comma separated list





~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Get the mailserver that powers this list at 
http://www.coolfusion.com

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

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

2003-07-02 Thread DURETTE, STEVEN J (AIT) 
Yes and No.  First, I misunderstood, I thought that the list was being
passed, not already in the table.

The dbo.listToTable function is one that I created.

It returns a 2 column table with the following columns: Position, Value

So the corrected version (now that I'm learning to read) should be:

SELECT EmailAddr, RecipientID
FROM SupportEmailRecipient
WHERE '#Form.SubjectID#' in (SELECT VALUE
FROM dbo.listToTable(SubjectID))


Steve


-Original Message-
From: Philip Arnold [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 02, 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#'))

 If you want the functions, let me know.

Shouldn't that be

WHERE #form.SubjectID# IN (dbo.listToTable(SubjectID))

As there's one entry in form.SubjectID and SubjectID in the table is a
comma separated list






~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

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

2003-07-02 Thread S . Isaac Dealey 
Yea I hosed it up the first time I replied... You don't really need the OR's tho, you 
just need to concatenate a , on the beginning and end of the column, so you can use 
WHERE ',' + SubjectID + ',' LIKE '%,#form.subjectid#,%' assuming of course that your 
db server uses + for concatenation... And actually you don't want to use the OR's 
because if you use for instance OR SubjectID LIKE '%,#form.subjectid#%' and you're 
looking for subject 1 this will match '2,1' and it will also match '2,10' ... Of 
course, you can leave off the trailing % but it's so much easier just to concatenate 
the column...

 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
 cfif ListLen(Form.SubjectID)
 WHERE
 (SubjectID LIKE '%#FORM.SubjectID#,%'
 OR
 SubjectID LIKE '%,#FORM.SubjectID#,%'
 OR
 SubjectID LIKE '%,#FORM.SubjectID#%'
 OR
 SubjectID = '%#FORM.SubjectID#%')
 /cfif

 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 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:

 SELECT  EmailAddr, RecipientID
 FROMSupportEmailRecipient
 WHERE   SubjectID LIKE '%,#FORM.SubjectID#,%'

 I'm not storing comma delimited lists in the database,
 but rather, in
 cases where I need or want an IN () statement that I
 couldn't otherwise
 accomplish by passing a list to a stored procedure (
 because I want to
 continue using a stored procedure ) I'm passing the list
 and selecting WHERE
 @mylist LIKE '%,' + cast(mycolumn as nvarchar) + ',%'
 which seems to work
 fairly well...

 hth

  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 database with data
  like:
  1,3,9,12,2,51

  LF Have a formfield with single number (not digit)
  data,
  like:  1 or 12  --
  LF single whole numbers, maybe more than 1 digit.

  LF Now, would like to do something like this:

  LF SELECT  EmailAddr, RecipientID
  LF FROMSupportEmailRecipient
  LF WHERE   SubjectID IN ('#FORM.SubjectID#)

  LF It ain't workin... Need to see if the data from the
  formfield is IN the data
  LF in the table's record.

  ~~~
  ~~~
  ~~~|
  Archives:
  http://www.houseoffusion.com/cf_lists/index.cfm?forumid
  =4
  Subscription:
  http://www.houseoffusion.com/cf_lists/index.
  cfm?method=subscribeforumid=4
  FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

  Signup for the Fusion Authority news alert and keep up
  with the latest news in ColdFusion and related topics.
  http://www.fusionauthority.com/signup.cfm

  Unsubscribe: http://www.houseoffusion.com/cf_lists/uns
  ubscribe.cfm?user=633.558.4




 s. isaac dealey972-490-6624

 new epoch  http://www.turnkey.to

 lead architect, tapestry cms   http://products.turnkey.to

 tapestry api is opensource http://www.turnkey.to/tapi

 certified advanced coldfusion 5 developer
 http://www.macromedia.com/v1/handlers/index.cfm?ID=21816



 ~~
 ~~~|
 Archives:
 http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
 Subscription: http://www.houseoffusion.com/cf_lists/index.
 cfm?method=subscribeforumid=4
 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

 Your ad could be here. Monies from ads go to support these
 lists and provide more resources for the community.
 http://www.fusionauthority.com/ads.cfm

   Unsubscribe: http://www.houseoffusion.com/cf_lists/uns
   ubscribe.cfm?user=633.558.4




s. isaac dealey972-490-6624

new epoch  http://www.turnkey.to

lead architect, tapestry cms   http://products.turnkey.to

tapestry api is opensource http://www.turnkey.to/tapi

certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

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, RecipientID
FROMSupportEmailRecipient 
WHERE   SubjectID IN ('#FORM.SubjectID#)

It ain't workin... Need to see if the data from the formfield is IN the data
in the table's record.

Help?

TTAIA


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

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 database with data like:  1,3,9,12,2,51

LF Have a formfield with single number (not digit) data, like:  1 or 12  --
LF single whole numbers, maybe more than 1 digit.

LF Now, would like to do something like this:

LF SELECT  EmailAddr, RecipientID
LF FROMSupportEmailRecipient 
LF WHERE   SubjectID IN ('#FORM.SubjectID#)

LF It ain't workin... Need to see if the data from the formfield is IN the data
LF in the table's record.

~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. 
http://www.fusionauthority.com/signup.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

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 database, but rather, in cases where I 
need or want an IN () statement that I couldn't otherwise accomplish by passing a list 
to a stored procedure ( because I want to continue using a stored procedure ) I'm 
passing the list and selecting WHERE @mylist LIKE '%,' + cast(mycolumn as nvarchar) + 
',%' which seems to work fairly well...

hth

 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 database with data like:
 1,3,9,12,2,51

 LF Have a formfield with single number (not digit) data,
 like:  1 or 12  --
 LF single whole numbers, maybe more than 1 digit.

 LF Now, would like to do something like this:

 LF SELECT  EmailAddr, RecipientID
 LF FROMSupportEmailRecipient
 LF WHERE   SubjectID IN ('#FORM.SubjectID#)

 LF It ain't workin... Need to see if the data from the
 formfield is IN the data
 LF in the table's record.

 ~~
 ~~~|
 Archives:
 http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
 Subscription: http://www.houseoffusion.com/cf_lists/index.
 cfm?method=subscribeforumid=4
 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

 Signup for the Fusion Authority news alert and keep up
 with the latest news in ColdFusion and related topics.
 http://www.fusionauthority.com/signup.cfm

   Unsubscribe: http://www.houseoffusion.com/cf_lists/uns
   ubscribe.cfm?user=633.558.4




s. isaac dealey972-490-6624

new epoch  http://www.turnkey.to

lead architect, tapestry cms   http://products.turnkey.to

tapestry api is opensource http://www.turnkey.to/tapi

certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816


~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq

Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. 
http://www.fusionauthority.com/ads.cfm

Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4