RE: Compare Two Lists
Mark,
Perhaps I'm missing something... but it seems like you already have your
list to delete. Why are you comparing again? Why not just do:
DELETE FROM someDBTable
WHERE emailaddress IN ('# todelete#')
Using cfqueryparam of course :)
Is the list too large? Sometimes the driver will only handle n number of
items in a list - something like 16k I think.
-Mark
-Original Message-
From: i...@markleder.com i...@markleder.com [mailto:i...@markleder.com]
Sent: Wednesday, July 17, 2013 9:02 AM
To: cf-talk
Subject: Compare Two Lists
Hi all,
Attempting to clean up a table with email records. In addition to the
existing table of good and bad email addresses, I have a csv file with
addresses that would need to be matched up to the db table, then deleted (so
I just have good addresses).
Email addresses are properly formatted in both the db and in the csv, so I
won't have to deal with duplicate addresses or hang ups because of improper
formatting. The db table has about 10,000 records. The csv file has about
4,000 addresses that will need to be matched and then dropped from the db
table. Each recordset only has email addresses, so we're only dealing with
one column.
I've been working with the following, but I'm not sure this is the best way
to go about it, or if it will function properly. And, I'm not sure that the
ListGetAt or the delete WHERE clause will work. Conceptually for some
reason I have a mental block about arrays.
Thanks for your advise. Mark
!--- Get the existing db email addresses ---
cfquery name=getTableAddresses datasource=#dsnSQL# username=#dsnUID#
password=#dsnPWD#
SELECT emailaddress
FROM someDBTable
ORDER BY emailaddress asc
/cfquery
!--- Get the addresses which are bad from the csv file ---
cfhttp
name=cleanedAddressestoDelete
method=get
url=http://www.somesite.com/file.csv;
/
cfset oldList = ValueList(getTableAddresses.emailaddress)
cfset todelete = ValueList(cleanedAddressestoDelete.emailaddress)
cfloop From=1 To=#ListLen(oldList)# index=emailaddress
cfif ListGetAt(oldList, emailaddress) EQ
ListGetAt(todelete,emailaddress)
cfquery name=deleteAddress datasource=#dsnSQL# username=#dsnUID#
password=#dsnPWD#
DELETE FROM someDBTable
WHERE emailaddress = #todelete.emailaddress#
/cfquery
/cfif
/cfloop
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356203
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Compare Two Lists
Sure seems like I make things more complicated than they need to be. If the simple delete statement as you show will drop out the 4000 records without looping, then great! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356212 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Compare Two Lists
Not exactly It should look like this mark DELETE FROM someDBTable WHERE emailaddress IN (cfqueryparam cfsqltype=CF_SQL_CHAR value=#todelete# list=yes/) I left off the syntax for expediency :) Be sure this is what you want to do! -Original Message- From: i...@markleder.com i...@markleder.com [mailto:i...@markleder.com] Sent: Wednesday, July 17, 2013 10:08 AM To: cf-talk Subject: Re: Compare Two Lists Sure seems like I make things more complicated than they need to be. If the simple delete statement as you show will drop out the 4000 records without looping, then great! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356214 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Compare Two Lists
FWIW: I have 4000 records to delete, with CF 9, throws an error: [Macromedia][SQLServer JDBC Driver][SQLServer]The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. I can break up the routines into three sets to make it work. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356224 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Compare Two Lists
Depending on where this CSV file is coming from, I'd just not use cfqueryparam. The only real reason to use it in a case like this is to make sure nobody's SQL injecting you. If this is an automated process and you think maybe one day you might not be able to trust the inputs form the CVS file, then you need to still worry. But it's it's just a one time thing, just use the good ole SQL syntax: WHERE emailAddress in (#todelete#) I'd stick to SQL too - comparing lists of this length in CF is going to be a really crappy slow experience. -Cameron On Wed, Jul 17, 2013 at 3:00 PM, i...@markleder.com i...@markleder.com i...@markleder.com wrote: FWIW: I have 4000 records to delete, with CF 9, throws an error: [Macromedia][SQLServer JDBC Driver][SQLServer]The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. I can break up the routines into three sets to make it work. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356226 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Compare Two Lists
What database platform are you on? I'd import those email addresses in the CSV to a temporary table and then just use a sub-select in the where clause. Let the database do the work... DELETE FROM someTable WHERE emailaddress in (SELECT email from tempImportTable) Also, if you are on SQL Server and this is a routine that you will need to do every so often, create a SSIS Package to run the import from CSV and clean up the the table. They are pretty easy to create and all you need to do is refresh the CSV file for it to run. --Jeff Original Message From: i...@markleder.com i...@markleder.com i...@markleder.com Sent: Wednesday, July 17, 2013 12:01 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: Compare Two Lists FWIW: I have 4000 records to delete, with CF 9, throws an error: [Macromedia][SQLServer JDBC Driver][SQLServer]The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. I can break up the routines into three sets to make it work. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356225 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Compare Two Lists
If you do it this way you need listQualify() cfset toDelete = listqualify(toDelete,)/ Then you will need to preservesinglequotes in the query as in... WHERE emailAddress IN (#preservesinglequotes(todelete)#) That's one of the reasons that I USE cfqueryparam - to keep me from having to write list code :) -Mark -Original Message- From: Cameron Childress [mailto:camer...@gmail.com] Sent: Wednesday, July 17, 2013 2:10 PM To: cf-talk Subject: Re: Compare Two Lists Depending on where this CSV file is coming from, I'd just not use cfqueryparam. The only real reason to use it in a case like this is to make sure nobody's SQL injecting you. If this is an automated process and you think maybe one day you might not be able to trust the inputs form the CVS file, then you need to still worry. But it's it's just a one time thing, just use the good ole SQL syntax: WHERE emailAddress in (#todelete#) I'd stick to SQL too - comparing lists of this length in CF is going to be a really crappy slow experience. -Cameron On Wed, Jul 17, 2013 at 3:00 PM, i...@markleder.com i...@markleder.com i...@markleder.com wrote: FWIW: I have 4000 records to delete, with CF 9, throws an error: [Macromedia][SQLServer JDBC Driver][SQLServer]The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. I can break up the routines into three sets to make it work. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356230 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Compare Two Lists
On Wed, Jul 17, 2013 at 5:09 PM, Mark A Kruger wrote: That's one of the reasons that I USE cfqueryparam - to keep me from having to write list code :) Agreed. It's rare that I recommend against cfqueryparam, but in this case I think it might be easier. -Cameron ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356232 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Compare ftp and directories
On Thu, May 23, 2013 at 8:56 AM, Phillip Vector wrote: Can anyone suggest a program that will read an ftp directory (and sub directories) and show what files are different? I suspect BeyondCompare does this. -Cameron ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355831 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Compare ftp and directories
yes it does :-) On Thu, May 23, 2013 at 2:20 PM, Cameron Childress camer...@gmail.comwrote: On Thu, May 23, 2013 at 8:56 AM, Phillip Vector wrote: Can anyone suggest a program that will read an ftp directory (and sub directories) and show what files are different? I suspect BeyondCompare does this. -Cameron ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355832 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Compare ftp and directories
Coolcool. Thanks. The contract I am on has no version control, so I needed this so I can keep straight what I uploaded and such. Thanks again guys. On May 23, 2013 6:25 AM, Russ Michaels r...@michaels.me.uk wrote: yes it does :-) On Thu, May 23, 2013 at 2:20 PM, Cameron Childress camer...@gmail.com wrote: On Thu, May 23, 2013 at 8:56 AM, Phillip Vector wrote: Can anyone suggest a program that will read an ftp directory (and sub directories) and show what files are different? I suspect BeyondCompare does this. -Cameron ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355833 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: compare using cfcase
you mean CFCASE not CFACASE :-) to condition multiple values use a list cfcase value=1,2,3 Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340628 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: compare using cfcase
Thanks Russ, That gives me an error (below) when I tried cfcase value = 0,1 to check whether String 1 is equal to or greater than String 2 Context validation error for tag CFCASE. The CFSWITCH has a duplicate CFCASE for value 1.0. ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem. RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:13pm To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase you mean CFCASE not CFACASE :-) to condition multiple values use a list cfcase value=1,2,3 Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340629 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: compare using cfcase
Have you got multiple CFCASES with the same value ? -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:19 To: cf-talk Subject: Re: compare using cfcase Thanks Russ, That gives me an error (below) when I tried cfcase value = 0,1 to check whether String 1 is equal to or greater than String 2 Context validation error for tag CFCASE. The CFSWITCH has a duplicate CFCASE for value 1.0. ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem. RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:13pm To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase you mean CFCASE not CFACASE :-) to condition multiple values use a list cfcase value=1,2,3 Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340631 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: compare using cfcase
No Russ, Here is the full code below. I would like one of the fields to test for eq to or gt: cfif IsDefined(FORM.string1) cfset comparison = Compare(FORM.string1, FORM.string2) !--- Switch on the variable to give various responses. --- cfswitch expression = #comparison# cfcase value = -1 h3String 1 is less than String 2/h3 IThe strings are not equal/I /cfcase cfcase value = 0 h3String 1 is equal to or gt than String 2/h3 IThe strings are equal!/I /cfcase cfcase value = 1 h3String 1 is greater than String 2/h3 IThe strings are not equal/I /cfcase cfdefaultcase h3This is the default case/h3 /cfdefaultcase /cfswitch /cfif RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:22pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase Have you got multiple CFCASES with the same value ? -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:19 To: cf-talk Subject: Re: compare using cfcase Thanks Russ, That gives me an error (below) when I tried cfcase value = 0,1 to check whether String 1 is equal to or greater than String 2 Context validation error for tag CFCASE. The CFSWITCH has a duplicate CFCASE for value 1.0. ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem. RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:13pm To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase you mean CFCASE not CFACASE :-) to condition multiple values use a list cfcase value=1,2,3 Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340632 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: compare using cfcase
If you have cfcase value=0,1 And cfcase value=1 Then you are duplicating the condition as you have 1 in both cfcases. The value must only appear once. Russ -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:29 To: cf-talk Subject: RE: compare using cfcase No Russ, Here is the full code below. I would like one of the fields to test for eq to or gt: cfif IsDefined(FORM.string1) cfset comparison = Compare(FORM.string1, FORM.string2) !--- Switch on the variable to give various responses. --- cfswitch expression = #comparison# cfcase value = -1 h3String 1 is less than String 2/h3 IThe strings are not equal/I /cfcase cfcase value = 0 h3String 1 is equal to or gt than String 2/h3 IThe strings are equal!/I /cfcase cfcase value = 1 h3String 1 is greater than String 2/h3 IThe strings are not equal/I /cfcase cfdefaultcase h3This is the default case/h3 /cfdefaultcase /cfswitch /cfif RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:22pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase Have you got multiple CFCASES with the same value ? -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:19 To: cf-talk Subject: Re: compare using cfcase Thanks Russ, That gives me an error (below) when I tried cfcase value = 0,1 to check whether String 1 is equal to or greater than String 2 Context validation error for tag CFCASE. The CFSWITCH has a duplicate CFCASE for value 1.0. ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem. RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:13pm To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase you mean CFCASE not CFACASE :-) to condition multiple values use a list cfcase value=1,2,3 Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340633 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: compare using cfcase
OK, but I have not done that in the code below. I just want to do a less than or equal comparison. Can that be done using CFCASE? Can it be cfcase value=1 or cfcase value=0 RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:38pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase If you have cfcase value=0,1 And cfcase value=1 Then you are duplicating the condition as you have 1 in both cfcases. The value must only appear once. Russ -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:29 To: cf-talk Subject: RE: compare using cfcase No Russ, Here is the full code below. I would like one of the fields to test for eq to or gt: cfif IsDefined(FORM.string1) cfset comparison = Compare(FORM.string1, FORM.string2) !--- Switch on the variable to give various responses. --- cfswitch expression = #comparison# cfcase value = -1 h3String 1 is less than String 2/h3 IThe strings are not equal/I /cfcase cfcase value = 0 h3String 1 is equal to or gt than String 2/h3 IThe strings are equal!/I /cfcase cfcase value = 1 h3String 1 is greater than String 2/h3 IThe strings are not equal/I /cfcase cfdefaultcase h3This is the default case/h3 /cfdefaultcase /cfswitch /cfif RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:22pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase Have you got multiple CFCASES with the same value ? -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:19 To: cf-talk Subject: Re: compare using cfcase Thanks Russ, That gives me an error (below) when I tried cfcase value = 0,1 to check whether String 1 is equal to or greater than String 2 Context validation error for tag CFCASE. The CFSWITCH has a duplicate CFCASE for value 1.0. ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem. RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:13pm To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase you mean CFCASE not CFACASE :-) to condition multiple values use a list cfcase value=1,2,3 Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340634 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: compare using cfcase
Try setting a var and then using _that_ in your cfcase. cfset isLTE = false / cfif string1 LTE string2 cfset isLTE = true / /cfif cfswitch expression = #isLTE# cfcase value = true h3String 1 is less than or equal to String 2/h3 IThe strings are not equal/I /cfcase cfcase value = false h3String 1 is gt than String 2/h3 /cfcase cfdefaultcase h3This is the default case/h3 /cfdefaultcase /cfswitch /cfif On Tue, Jan 11, 2011 at 2:44 PM, Orlini, Robert rorl...@hwwilson.comwrote: OK, but I have not done that in the code below. I just want to do a less than or equal comparison. Can that be done using CFCASE? Can it be cfcase value=1 or cfcase value=0 RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:38pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase If you have cfcase value=0,1 And cfcase value=1 Then you are duplicating the condition as you have 1 in both cfcases. The value must only appear once. Russ -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:29 To: cf-talk Subject: RE: compare using cfcase No Russ, Here is the full code below. I would like one of the fields to test for eq to or gt: cfif IsDefined(FORM.string1) cfset comparison = Compare(FORM.string1, FORM.string2) !--- Switch on the variable to give various responses. --- cfswitch expression = #comparison# cfcase value = -1 h3String 1 is less than String 2/h3 IThe strings are not equal/I /cfcase cfcase value = 0 h3String 1 is equal to or gt than String 2/h3 IThe strings are equal!/I /cfcase cfcase value = 1 h3String 1 is greater than String 2/h3 IThe strings are not equal/I /cfcase cfdefaultcase h3This is the default case/h3 /cfdefaultcase /cfswitch /cfif RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:22pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase Have you got multiple CFCASES with the same value ? -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:19 To: cf-talk Subject: Re: compare using cfcase Thanks Russ, That gives me an error (below) when I tried cfcase value = 0,1 to check whether String 1 is equal to or greater than String 2 Context validation error for tag CFCASE. The CFSWITCH has a duplicate CFCASE for value 1.0. ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem. RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:13pm To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase you mean CFCASE not CFACASE :-) to condition multiple values use a list cfcase value=1,2,3 Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340635 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: compare using cfcase
OR just use the list nature of CFCASE: cfcase value=-1 string 1 is neither equal nor greater /cfcase cfcase value=0,1 string 1 is equal or greater /cfcase From: Michael Grant mgr...@modus.bz Sent: Tuesday, January 11, 2011 2:59 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase Try setting a var and then using _that_ in your cfcase. cfset isLTE = false / cfif string1 LTE string2 cfset isLTE = true / /cfif cfswitch expression = #isLTE# cfcase value = true h3String 1 is less than or equal to String 2/h3 IThe strings are not equal/I /cfcase cfcase value = false h3String 1 is gt than String 2/h3 /cfcase cfdefaultcase h3This is the default case/h3 /cfdefaultcase /cfswitch /cfif On Tue, Jan 11, 2011 at 2:44 PM, Orlini, Robert rorl...@hwwilson.comwrote: OK, but I have not done that in the code below. I just want to do a less than or equal comparison. Can that be done using CFCASE? Can it be cfcase value=1 or cfcase value=0 RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:38pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase If you have cfcase value=0,1 And cfcase value=1 Then you are duplicating the condition as you have 1 in both cfcases. The value must only appear once. Russ -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:29 To: cf-talk Subject: RE: compare using cfcase No Russ, Here is the full code below. I would like one of the fields to test for eq to or gt: cfif IsDefined(FORM.string1) cfset comparison = Compare(FORM.string1, FORM.string2) !--- Switch on the variable to give various responses. --- cfswitch expression = #comparison# cfcase value = -1 h3String 1 is less than String 2/h3 IThe strings are not equal/I /cfcase cfcase value = 0 h3String 1 is equal to or gt than String 2/h3 IThe strings are equal!/I /cfcase cfcase value = 1 h3String 1 is greater than String 2/h3 IThe strings are not equal/I /cfcase cfdefaultcase h3This is the default case/h3 /cfdefaultcase /cfswitch /cfif RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:22pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase Have you got multiple CFCASES with the same value ? -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:19 To: cf-talk Subject: Re: compare using cfcase Thanks Russ, That gives me an error (below) when I tried cfcase value = 0,1 to check whether String 1 is equal to or greater than String 2 Context validation error for tag CFCASE. The CFSWITCH has a duplicate CFCASE for value 1.0. ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem. RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:13pm To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase you mean CFCASE not CFACASE :-) to condition multiple values use a list cfcase value=1,2,3 Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340636 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: compare using cfcase
Jason, That is what I have already suggested, but for some reason it is throwing an error. It is definitely valid syntax for a CFCASE statement. Which version of CF are you using. can you show us the exact error and section of code it relates to, On Tue, Jan 11, 2011 at 8:22 PM, Jason Fisher ja...@wanax.com wrote: OR just use the list nature of CFCASE: cfcase value=-1 string 1 is neither equal nor greater /cfcase cfcase value=0,1 string 1 is equal or greater /cfcase From: Michael Grant mgr...@modus.bz Sent: Tuesday, January 11, 2011 2:59 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase Try setting a var and then using _that_ in your cfcase. cfset isLTE = false / cfif string1 LTE string2 cfset isLTE = true / /cfif cfswitch expression = #isLTE# cfcase value = true h3String 1 is less than or equal to String 2/h3 IThe strings are not equal/I /cfcase cfcase value = false h3String 1 is gt than String 2/h3 /cfcase cfdefaultcase h3This is the default case/h3 /cfdefaultcase /cfswitch /cfif On Tue, Jan 11, 2011 at 2:44 PM, Orlini, Robert rorl...@hwwilson.comwrote: OK, but I have not done that in the code below. I just want to do a less than or equal comparison. Can that be done using CFCASE? Can it be cfcase value=1 or cfcase value=0 RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:38pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase If you have cfcase value=0,1 And cfcase value=1 Then you are duplicating the condition as you have 1 in both cfcases. The value must only appear once. Russ -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:29 To: cf-talk Subject: RE: compare using cfcase No Russ, Here is the full code below. I would like one of the fields to test for eq to or gt: cfif IsDefined(FORM.string1) cfset comparison = Compare(FORM.string1, FORM.string2) !--- Switch on the variable to give various responses. --- cfswitch expression = #comparison# cfcase value = -1 h3String 1 is less than String 2/h3 IThe strings are not equal/I /cfcase cfcase value = 0 h3String 1 is equal to or gt than String 2/h3 IThe strings are equal!/I /cfcase cfcase value = 1 h3String 1 is greater than String 2/h3 IThe strings are not equal/I /cfcase cfdefaultcase h3This is the default case/h3 /cfdefaultcase /cfswitch /cfif RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:22pm To: cf-talk cf-talk@houseoffusion.com Subject: RE: compare using cfcase Have you got multiple CFCASES with the same value ? -Original Message- From: Orlini, Robert [mailto:rorl...@hwwilson.com] Sent: 11 January 2011 19:19 To: cf-talk Subject: Re: compare using cfcase Thanks Russ, That gives me an error (below) when I tried cfcase value = 0,1 to check whether String 1 is equal to or greater than String 2 Context validation error for tag CFCASE. The CFSWITCH has a duplicate CFCASE for value 1.0. ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem. RO -Original Message- From: Russ Michaels r...@michaels.me.uk Sent: Tuesday, January 11, 2011 2:13pm To: cf-talk cf-talk@houseoffusion.com Subject: Re: compare using cfcase you mean CFCASE not CFACASE :-) to condition multiple values use a list cfcase value=1,2,3 Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340640 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: compare using cfcase
I know this is not a direct answer to your question, but in your scenario you should not be using cfcase. You should be using cfif/cfelse since there are only two possible conditions. Also remember that the Compare function is case-sensitive. -Mike Chabot On Tue, Jan 11, 2011 at 2:08 PM, Orlini, Robert rorl...@hwwilson.comwrote: Hi, I have to compare 2 values. I'm suing the cfacase and compare functions. I have them comapring well if they either less than in one comparision or equal to in another. How would I combine the cfcase to test whether the values are equal or less than? I tried cfcase value = -1 or cfcase value = 0. but I figured that would not work. See below. Thanks. RO HWW cfset comparison = Compare(FORM.string1, FORM.string2) !--- Switch on the variable to give various responses. --- cfswitch expression = #comparison# cfcase value = -1 h3String 1 is less than String 2/h3 IThe strings are not equal/I /cfcase cfcase value = 0 h3String 1 is equal to String 2/h3 IThe strings are equal!/I /cfcase /cfswitch ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340641 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Compare a Record and insert null values
So you want to have 'n/a' as default if there is no measurement?. T-SQL has a COALESCE function which can be used to check whether the column is NULL and have a default value. Example: COALESCE(table.columnName,n/a) as anyName. if table.columnName is NULL, then n/a will be substituted. Hope this is what you are looking for. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328838 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare a Record and insert null values
So you want to have 'n/a' as default if there is no measurement?. T-SQL has a COALESCE function which can be used to check whether the column is NULL and have a default value. Example: COALESCE(table.columnName,n/a) as anyName. if table.columnName is NULL, then n/a will be substituted. Hope this is what you are looking for. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328839 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare a Record and insert null values
Thanks for you response raj, however the column would not have been created at that point. If a user enters the first part no. with say three measurements a=1,b=2,c=4 that accounts for three rows against the specific part no., when he enters a second part no. with four measurements i.e. a=1,b=2,c=3,d=4. I need the code to loop through all the part numbers, find the part no. with the most measurements assigned to it enter the missing value in all the part no. measurements. i.e. part no.1 a=1,b=2,c=3,d=4,e=5 part no.2 a=1,b=2,c=3,d=4,e=5,e=6 part no.3 a=1,b=2,c=3 so part no 2 has the most measurements, now letâs update part no 1 and 3 with the same amount of measurements using the same title i.e. part no.1 a=1,b=2,c=3,d=4,e=5,e=n/a part no.2 a=1,b=2,c=3,d=4,e=5,e=6 part no.3 a=1,b=2,c=3,d=n/a,e=n/a I hope this explains it a little better. Jason ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328872 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare a Record and insert null values
Hi I managed to acheive what i'm after, however its a seems bit intensive due to the loop factor, if there are any better ways i would love to hear them. !//Step 1: Get the product in question--- cfquery name=get_products datasource=#application.dsn# SELECT products.productID, products.psubCatID, products.productName, products.productDescription FROM products WHERE productID = cfqueryparam value=#prodID# cfsqltype=cf_sql_integer /cfquery !---//Step 2: Get the related product parts and measurements--- cfquery name=get_meas datasource=#application.dsn# SELECT pp.productPartID, pp.partProdID, pp.partNo, m.mTitle, m.measOne FROM productPart pp INNER JOIN measurement m ON pp.productPartID = m.mPartID WHERE pp.partProdID = #get_products.productID# /cfquery !---//Loop through the measurements--- cfloop query=get_meas !---//Step 3: Get the all the measurement letters for every part no i.e a,b,c,d,e etc, using the in operator should return every letter used by the product part--- cfquery name=get_alpha datasource=#application.dsn# SELECT tableLetter FROM tableSort WHERE tableLetter IN (SELECT mTitle FROM measurement WHERE mPartID IN (SELECT productPartID FROM productPart WHERE partProdID = cfqueryparam value=#get_products.productID# cfsqltype=cf_sql_integer)) ORDER BY tableLetter ASC /cfquery !---//Loop through the letters--- cfloop query=get_alpha !---//Step 4: Find the missing letters looping through the get_alpha query for each part no--- cfquery name=get_final datasource=#application.dsn# SELECT m.mTitle, m.mPartID FROM measurement m WHERE m.mTitle = cfqueryparam cfsqltype=cf_sql_clob value=#get_alpha.tableLetter# AND m.mpartID = cfqueryparam cfsqltype=cf_sql_integer value=#get_meas.productPartID# /cfquery !---//Step 5: If the get_final query is empty, we know we can insert the letter and 0 values--- cfif get_final.recordcount EQ 0 cfquery name=ins datasource=#application.dsn# INSERT INTO measurement (mtitle, measOne, mpartID) VALUES (cfqueryparam cfsqltype=cf_sql_clob value=#get_alpha.tableLetter#, cfqueryparam cfsqltype=cf_sql_integer value=0, cfqueryparam cfsqltype=cf_sql_integer value=#get_meas.productPartID#) /cfquery /cfif /cfloop!---//get_alpha--- /cfloop!---//get_meas--- ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328887 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare records and update
Hi Just wondered if anyone had any ideas? Jason Thank you again for your response, sorry for time its taking for me to get my head around this!! Have a look at this http://tvpressings.jasoncongerton.co.uk/transteel-products/test. cfm?productid=2 all looks good, however no NA in boxes with no measurements. however this is only becauase the top two rows contains the top most amount of measurements, all the way to T now look at this, i have placed the letters above the measurment to show they are out of align with the top headers. http://tvpressings.jasoncongerton.co.uk/transteel-products/test. cfm?productid=29 I need the ooutput to align with the top headers ABC. code below Jason !---get the measurements and parts no's--- cfquery name=get_parts datasource=#application.dsn# SELECT p.lamNo, p.coreNo, p.pattern, p.productPartID, p.partNo, m.measOne, m.measTwo, m.mTitle FROM productPart p INNER JOIN measurement m ON p.productPartID = m.mPartID WHERE p.partProdID = cfqueryparam value=#productID# cfsqltype=cf_sql_integer ORDER BY p.order, p.lamNo, p.coreNo, p.pattern, m.mtitle /cfquery !---//get the letters for the top most row--- cfquery name=get_alpha datasource=#application.dsn# SELECT tableLetter FROM tableSort WHERE tableLetter IN (SELECT mTitle FROM measurement WHERE mPartID IN (SELECT productPartID FROM productPart WHERE partProdID = cfqueryparam value=#productID# cfsqltype=cf_sql_integer)) ORDER BY tableLetter ASC /cfquery table tr td cfif get_parts.lamNo NEQ LAM NO:/cfifcfif get_parts.coreNo NEQ CORE NO:/cfif cfif get_parts.pattern NEQ Pattern:/cfif/td tdstrongPART NO:/strong/td cfoutput query=get_alpha td style=width:70px; font-family:Verdana, Geneva, sans-serif; font-size:10px; text-align:center;strong#tableLetter#/strong/td /cfoutput /tr cfoutput query=get_parts group=lamNo td#lamNo#/td tdstrong#partNo#/strong/td cfset ctr = 0 / cfoutput group=mTitle td#mTitle#br /#decimalFormat(measOne)#cfif measTwo GT 0 X #decimalFormat(measTwo)#/cfif/td cfset ctr = ctr+1 / /cfoutput cfif ctr lt get_alpha.recordcount cfloop from=#ctr# to=#get_alpha.recordcount# index=c tdnbsp;/td /cfloop /cfif /tr /cfoutput /cfif /table /tr cfoutput query=get_parts group=lamNo td style=width:80px; font-family:Verdana, Geneva, sans-serif; font-size:10px;strong#lamNo#/strong/td td style=width:80px; font-family:Verdana, Geneva, sans-serif; font-size:10px;strong#partNo#/strong/td cfset ctr = 0 / cfoutput group=mTitle td style=width:70px; font-family:Verdana, Geneva, sans-serif; font-size:10px; text-align:center;#mTitle#br /#decimalFormat(measOne)#cfif measTwo GT 0 X #decimalFormat(measTwo)#/cfif/td cfset ctr = ctr+1 / /cfoutput cfif ctr lt get_alpha.recordcount cfloop from=#ctr# to=#get_alpha.recordcount# index=c tdnbsp;/td /cfloop /cfif /tr /cfoutput /cfif /table ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328386 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare records and update
Thank you again for your response, sorry for time its taking for me to get my head around this!! Have a look at this http://tvpressings.jasoncongerton.co.uk/transteel-products/test.cfm?productid=2 all looks good, however no NA in boxes with no measurements. however this is only becauase the top two rows contains the top most amount of measurements, all the way to T now look at this, i have placed the letters above the measurment to show they are out of align with the top headers. http://tvpressings.jasoncongerton.co.uk/transteel-products/test.cfm?productid=29 I need the ooutput to align with the top headers ABC. code below Jason !---get the measurements and parts no's--- cfquery name=get_parts datasource=#application.dsn# SELECT p.lamNo, p.coreNo, p.pattern, p.productPartID, p.partNo, m.measOne, m.measTwo, m.mTitle FROM productPart p INNER JOIN measurement m ON p.productPartID = m.mPartID WHERE p.partProdID = cfqueryparam value=#productID# cfsqltype=cf_sql_integer ORDER BY p.order, p.lamNo, p.coreNo, p.pattern, m.mtitle /cfquery !---//get the letters for the top most row--- cfquery name=get_alpha datasource=#application.dsn# SELECT tableLetter FROM tableSort WHERE tableLetter IN (SELECT mTitle FROM measurement WHERE mPartID IN (SELECT productPartID FROM productPart WHERE partProdID = cfqueryparam value=#productID# cfsqltype=cf_sql_integer)) ORDER BY tableLetter ASC /cfquery table tr td cfif get_parts.lamNo NEQ LAM NO:/cfifcfif get_parts.coreNo NEQ CORE NO:/cfif cfif get_parts.pattern NEQ Pattern:/cfif/td tdstrongPART NO:/strong/td cfoutput query=get_alpha td style=width:70px; font-family:Verdana, Geneva, sans-serif; font-size:10px; text-align:center;strong#tableLetter#/strong/td /cfoutput /tr cfoutput query=get_parts group=lamNo td#lamNo#/td tdstrong#partNo#/strong/td cfset ctr = 0 / cfoutput group=mTitle td#mTitle#br /#decimalFormat(measOne)#cfif measTwo GT 0 X #decimalFormat(measTwo)#/cfif/td cfset ctr = ctr+1 / /cfoutput cfif ctr lt get_alpha.recordcount cfloop from=#ctr# to=#get_alpha.recordcount# index=c tdnbsp;/td /cfloop /cfif /tr /cfoutput /cfif /table /tr cfoutput query=get_parts group=lamNo td style=width:80px; font-family:Verdana, Geneva, sans-serif; font-size:10px;strong#lamNo#/strong/td td style=width:80px; font-family:Verdana, Geneva, sans-serif; font-size:10px;strong#partNo#/strong/td cfset ctr = 0 / cfoutput group=mTitle td style=width:70px; font-family:Verdana, Geneva, sans-serif; font-size:10px; text-align:center;#mTitle#br /#decimalFormat(measOne)#cfif measTwo GT 0 X #decimalFormat(measTwo)#/cfif/td cfset ctr = ctr+1 / /cfoutput cfif ctr lt get_alpha.recordcount cfloop from=#ctr# to=#get_alpha.recordcount# index=c tdnbsp;/td /cfloop /cfif /tr /cfoutput /cfif /table ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328295 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare records and update
In your log where you output... add cfif trim(a) is NAcfelse#A#/cfif If you want to write it just once instead of for each field, evaluate the var name... A,B,C, etc. Robert B. Harrison Director of Interactive Services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be . Plug in to our blog: AW Unplugged http://www.austin-williams.com/unplugged -Original Message- From: Jason Congerton [mailto:ja...@jasoncongerton.co.uk] Sent: Wednesday, November 11, 2009 10:22 AM To: cf-talk Subject: Compare records and update Hi I have a table which stores measurements, the measurements have a corresponding letter assoiciated with them i.e A = 0.34 B = 0.35 etc. Measurements are directly related to the productPart table. The productPart table stores part no info relating to the product and measurements. One product main contain 1 or multiple part no's i.e Product: UFC-SLOTTED Part No: 21U, 22U, 23U, 24U Each part number has it's own set of measurments i.e Part No: 21U Measurements: A=0.23, B=0.34, C=0.23 Part No: 22U Measurements: A=0.23, B=0.34, C=0.23, D=0.45, E=0.46 As you can see the part numbers can use a varying amount of measurments, which is where my question comes. Part No: 21U has 3 measurments; A,B,C Part No: 22U has 5 measuremnts; A,B,C,D,E I need to fill the gaps automatically to produce the following and place NA or a null value. Part No: 21U Measurements: A=0.23, B=0.34, C=0.23, D=NA, E=NA, Part No: 22U Measurements: A=0.23, B=0.34, C=0.23, D=0.45, E=0.46 Once the user has completed inserting the measurements for the part no, i will offer a button to finalise product insert. I know i will need to loop around the the measurement table for each part no, but how do i know when to insert the correct title(i.e D) and the null value, what if statment will i use, i'm sure it must be possible. Table structure below; productPart productPartID(uid), partNo, lamNo measurement mID(uid),mPartID(link to productPart), mTitle, measOne Thanks Jason ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328270 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare records and update
Hi Robert Thanks for your reply, although I'm not sure if I understand your response. Looking at the code you supplied, will this not just display NA if a is empty, my problem is a might not be even created. part no 23a has 3 measurements: a=0.32,b=0.34,c=0.45 part no 24a has 2 measurements: a=0.12,b=0.43 for part no 24a, I would need to display c=na, although c does not exist for this part no. My final output should resemble this Part NO: A BCD 23a 0.32 0.34 0.35 0.36 23a 0.32 0.34 NA NA 23a NA0.34 0.12 0.34 If I have misunderstood you - sorry. Jason ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328279 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare records and update
Then, in your loop use: cfif isdefined(myvar) cfif trim(myvar) is #myvar#cfelseNA/cfif cfelse NA /cfif OK? Robert B. Harrison Director of Interactive Services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be . Plug in to our blog: AW Unplugged http://www.austin-williams.com/unplugged __ Information from ESET Smart Security, version of virus signature database 4597 (2009) __ The message was checked by ESET Smart Security. http://www.eset.com ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328280 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: compare arrays for same
http://cflib.org/udf/listVenn On Thu, Oct 15, 2009 at 6:05 AM, Richard White rich...@j7is.co.uk wrote: hi is there a function to compare 2 arrays or lists and return the values that exist in both? i have found plenty they return then difference but not for this case thanks ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327227 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: compare arrays for same
is there a function to compare 2 arrays or lists and return the values that exist in both? Not that I know of, but you could create your own. Though I have not looked, but I would be very surprised if cflib.org did not already have a function for this This could certainly be improved ... cfset list1 = a,b,c,d,e,f,g,h,i cfset list2 = 1,b,2,3,e,6,9 cfset shared = cfloop list=#list1# index=elem cfif listFindNoCase(list2, elem) cfset shared = listAppend(shared, elem) /cfif /cfloop cfoutput shared = #shared#br /cfoutput ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327229 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: compare arrays for same
neat function! thanks to all posts http://cflib.org/udf/listVenn On Thu, Oct 15, 2009 at 6:05 AM, Richard White rich...@j7is.co.uk wrote: ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327232 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: compare arrays for same
http://cflib.org/udf/listVenn Hah! I knew cflib.org must have a function for that ... ;-) ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327233 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Two MySQL Databases?
Marie Taylore said the following on 7/14/2009 6:24 PM: What's the easiest quickest way to compare two MySQL Schemas (databases)? Marie, You might want to check out MySQL Workbench: http://www.mysql.com/products/workbench/ Under the Change Management section of that page: To help DBAs and developers with change management, MySQL Workbench includes Schema Synchronization and Comparison utilities. A DBA can compare two live databases or a model and a live database and visually see the differences, and also perform a synchronization between a model and a live database or vice versa. -- Best regards, Chris Montgomery ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324522 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare Two MySQL Databases?
Take a structure-only (no data) mysqldump on both servers, and then use your choice of diff tools (command-line diff, Eclipse, BeyondCompare, etc.) to compare them. Certainly not a particularly elegant solution, but it's served me well in the past. cheers, barneyb On Tue, Jul 14, 2009 at 4:24 PM, Marie Tayloremt4yl...@yahoo.com wrote: What's the easiest quickest way to compare two MySQL Schemas (databases)? I don't need to compare the *data*... just the tables and their field definitions. My production instance is getting an error that my test instance is not. The code is identical, so I figure I must have left something out of the database updates, but going through all those tables and fields by hand will take forever. Just thought I'd see if anyone here has any tips. Thanks! MarieT ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324499 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Two MySQL Databases?
I've used SQLyog and found it to be pretty impressive. It's around $100, but there's a 30-day trial that might get you through this issue: http://www.webyog.com/en/downloads.php#sqlyog Edward Sonny Savage On Tue, Jul 14, 2009 at 7:24 PM, Marie Taylore mt4yl...@yahoo.com wrote: What's the easiest quickest way to compare two MySQL Schemas (databases)? I don't need to compare the *data*... just the tables and their field definitions. My production instance is getting an error that my test instance is not. The code is identical, so I figure I must have left something out of the database updates, but going through all those tables and fields by hand will take forever. Just thought I'd see if anyone here has any tips. Thanks! MarieT ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324500 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Two MySQL Databases?
+1 for SQLyog. SQLyog's schema and data sync tools are great IMHO. G! On Tue, Jul 14, 2009 at 7:33 PM, Sonny Savage sonnysav...@gmail.com wrote: I've used SQLyog and found it to be pretty impressive. It's around $100, but there's a 30-day trial that might get you through this issue: http://www.webyog.com/en/downloads.php#sqlyog Edward Sonny Savage On Tue, Jul 14, 2009 at 7:24 PM, Marie Taylore mt4yl...@yahoo.com wrote: What's the easiest quickest way to compare two MySQL Schemas (databases)? I don't need to compare the *data*... just the tables and their field definitions. My production instance is getting an error that my test instance is not. The code is identical, so I figure I must have left something out of the database updates, but going through all those tables and fields by hand will take forever. Just thought I'd see if anyone here has any tips. Thanks! MarieT ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324501 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare Two MySQL Databases?
I use this Stored Procedure to compare Tables: Overview: http://www.sql-server-performance.com/articles/dba/database_comparison_sp_p1 .aspx SP http://www.benzzon.se/forum/uploads/benzzon/2006-03-27_134824_sp_CompareDB.t xt Its free and it works well... Brook -Original Message- From: Sonny Savage [mailto:sonnysav...@gmail.com] Sent: July-14-09 4:34 PM To: cf-talk Subject: Re: Compare Two MySQL Databases? I've used SQLyog and found it to be pretty impressive. It's around $100, but there's a 30-day trial that might get you through this issue: http://www.webyog.com/en/downloads.php#sqlyog Edward Sonny Savage On Tue, Jul 14, 2009 at 7:24 PM, Marie Taylore mt4yl...@yahoo.com wrote: What's the easiest quickest way to compare two MySQL Schemas (databases)? I don't need to compare the *data*... just the tables and their field definitions. My production instance is getting an error that my test instance is not. The code is identical, so I figure I must have left something out of the database updates, but going through all those tables and fields by hand will take forever. Just thought I'd see if anyone here has any tips. Thanks! MarieT ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324502 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare Two MySQL Databases?
Sorry, that script was for MSSQL -Original Message- From: Barney Boisvert [mailto:bboisv...@gmail.com] Sent: July-14-09 4:31 PM To: cf-talk Subject: Re: Compare Two MySQL Databases? Take a structure-only (no data) mysqldump on both servers, and then use your choice of diff tools (command-line diff, Eclipse, BeyondCompare, etc.) to compare them. Certainly not a particularly elegant solution, but it's served me well in the past. cheers, barneyb On Tue, Jul 14, 2009 at 4:24 PM, Marie Tayloremt4yl...@yahoo.com wrote: What's the easiest quickest way to compare two MySQL Schemas (databases)? I don't need to compare the *data*... just the tables and their field definitions. My production instance is getting an error that my test instance is not. The code is identical, so I figure I must have left something out of the database updates, but going through all those tables and fields by hand will take forever. Just thought I'd see if anyone here has any tips. Thanks! MarieT ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324503 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Two MySQL Databases?
Sonny Gerald, Thanks for the suggestion! I downloaded it and I have a few questions: Can I have it just *show* me the differences instead of running a Sync? I don't want the *data* copied, I just want to know which field definitions might be different. Would you use database sync or schema sync for something like this? Or is there another compare option? Thanks! MarieT --- On Tue, 7/14/09, Gerald Guido gerald.gu...@gmail.com wrote: From: Gerald Guido gerald.gu...@gmail.com Subject: Re: Compare Two MySQL Databases? To: cf-talk cf-talk@houseoffusion.com Date: Tuesday, July 14, 2009, 4:53 PM +1 for SQLyog. SQLyog's schema and data sync tools are great IMHO. G! On Tue, Jul 14, 2009 at 7:33 PM, Sonny Savage sonnysav...@gmail.com wrote: I've used SQLyog and found it to be pretty impressive. It's around $100, but there's a 30-day trial that might get you through this issue: http://www.webyog.com/en/downloads.php#sqlyog Edward Sonny Savage On Tue, Jul 14, 2009 at 7:24 PM, Marie Taylore mt4yl...@yahoo.com wrote: What's the easiest quickest way to compare two MySQL Schemas (databases)? I don't need to compare the *data*... just the tables and their field definitions. My production instance is getting an error that my test instance is not. The code is identical, so I figure I must have left something out of the database updates, but going through all those tables and fields by hand will take forever. Just thought I'd see if anyone here has any tips. Thanks! MarieT ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324504 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Strings
Hi Charlie, This post is like long time ago .. but I also have a similar problem for comparision. Let me usse the same example: String1: 0010as1 String2: 10as1 .. this was solved using rereplace() but what if I actually need something like this. String1: 10as1 String1: 10as1121abc or 10as1abc or 10as1121 should be equal or String1: abc201 String1: abc20101 or abc201abc or abc20101dc or abc201dc01 should be equal It means that the string1 charecters should exactly match with String2 but string2 can have equal or more charecters than string1. Thanks in Advance, Priya I'm still learning regex, so i'm less concerned with inefficient and more concerned with holy crap it worked :) I had the + in place of the * initially, but read something that made me think the * would be better in that case. I probably misunderstood (and don't recall what it was specifically, as I read it late last night and I'm typing this early the next morning), but I think I get what you're saying. The + matches 1 or more, whereas the * matches zero or more... so with the asterisk the engine always hits the beginning and end (even in the case of zero zeros). Thanks. Just woke up and learned something. I can go back to bed :) -- I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322277 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare Strings
String1: abc201 String1: abc20101 or abc201abc or abc20101dc or abc201dc01 should be equal cfif string1 is left(string2, len(string1)) they are equal /cfif -Justin ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322278 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Strings
If I'm reading you right, you want the *first* part of String2 to match the entirety of String1...? If so, you could use the Left() function, i.e. cfif Len(string2) GE Len(string1) AND Left(string2, Len(string1)) IS string1 match /cfif If you're just looking to see if String1 is *in* String2, anywhere... FindNoCase(String1, String2) would suffice. --Scott Priya Koya wrote: Hi Charlie, This post is like long time ago .. but I also have a similar problem for comparision. Let me usse the same example: String1: 0010as1 String2: 10as1 .. this was solved using rereplace() but what if I actually need something like this. String1: 10as1 String1: 10as1121abc or 10as1abc or 10as1121 should be equal or String1: abc201 String1: abc20101 or abc201abc or abc20101dc or abc201dc01 should be equal It means that the string1 charecters should exactly match with String2 but string2 can have equal or more charecters than string1. Thanks in Advance, Priya ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322279 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Strings
O yes.. I didnt even thought of it . Thanx Justin. Priya. String1: abc201 String1: abc20101 or abc201abc or abc20101dc or abc201dc01 should be equal cfif string1 is left(string2, len(string1)) they are equal /cfif -Justin ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322280 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Strings
On Tue, Apr 7, 2009 at 12:12 AM, Pranathi Reddy rk.prana...@gmail.com wrote: I am comparing two strings and if I have 2 strings String1: 0010as1 String2: 10as1 I should get an output that these two strings are equal. If I use Trim,LTrim,Rtrim, compare().. what ever I use I am getting that strings are not equal... how do I compare these types of strings and display that these strings are equal. So... what rules are you working with? Are you saying that any leading/trailing zeros should be ignored? If so, rereplace(string1, '^0*|0*$', '', 'all') should remove leading/trailing zeros. So if you have: cfset string1 = 0010as1 / cfset string2 = 10as1 / cfoutput#compareNoCase(rereplace(string1, '^0*|0*$', '', 'all'), rereplace(string2, '^0*|0*$', '', 'all'))#/cfoutput should return 0 (no differences in the compareNoCase()) -- I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321384 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Strings
So... what rules are you working with? Are you saying that any leading/trailing zeros should be ignored? If so, rereplace(string1, '^0*|0*$', '', 'all') should remove leading/trailing zeros. H. Bit inefficient matching with * - that means you're always matching the positions ^ and $ whether there are any leading/trailing zeros or not. ^0+|0+$ would be better - only replacing when zeroes exist. It's a small change, but if this comparison is only part of a larger loop, it might have an impact. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321385 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Strings
Thanks Charlie and Peter for quick reponse... in either way I do ... I am getting -1 (string1 is less than string 2) Se below is that I did... cfset string1 = '0010as1000' cfset string2 = '10as1' cfoutput#compareNoCase(replace(string1,'^0*|0*$','','all'),rereplace(string2,'^0*|0*$','','all'))#/cfoutput or cfset string1 = '0010as1000' cfset string2 = '10as1' cfoutput#compareNoCase(replace(string1,'^0+|0$','','all'),rereplace(string2,'^0+|0$','','all'))#/cfoutput Thanks, Pranathi So... what rules are you working with? Are you saying that any leading/trailing zeros should be ignored? If so, rereplace(string1, '^0*|0*$', '', 'all') should remove leading/trailing zeros. H. Bit inefficient matching with * - that means you're always matching the positions ^ and $ whether there are any leading/trailing zeros or not. ^0+|0+$ would be better - only replacing when zeroes exist. It's a small change, but if this comparison is only part of a larger loop, it might have an impact. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321394 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare Strings
On Tue, Apr 7, 2009 at 12:48 AM, Peter Boughton bought...@gmail.com wrote: So... what rules are you working with? Are you saying that any leading/trailing zeros should be ignored? If so, rereplace(string1, '^0*|0*$', '', 'all') should remove leading/trailing zeros. H. Bit inefficient matching with * - that means you're always matching the positions ^ and $ whether there are any leading/trailing zeros or not. ^0+|0+$ would be better - only replacing when zeroes exist. It's a small change, but if this comparison is only part of a larger loop, it might have an impact. I'm still learning regex, so i'm less concerned with inefficient and more concerned with holy crap it worked :) I had the + in place of the * initially, but read something that made me think the * would be better in that case. I probably misunderstood (and don't recall what it was specifically, as I read it late last night and I'm typing this early the next morning), but I think I get what you're saying. The + matches 1 or more, whereas the * matches zero or more... so with the asterisk the engine always hits the beginning and end (even in the case of zero zeros). Thanks. Just woke up and learned something. I can go back to bed :) -- I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321402 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Strings
On Tue, Apr 7, 2009 at 7:53 AM, Pranathi Reddy rk.prana...@gmail.com wrote: Thanks Charlie and Peter for quick reponse... in either way I do ... I am getting -1 (string1 is less than string 2) Se below is that I did... cfset string1 = '0010as1000' cfset string2 = '10as1' cfoutput#compareNoCase(replace(string1,'^0*|0*$','','all'),rereplace(string2,'^0*|0*$','','all'))#/cfoutput or cfset string1 = '0010as1000' cfset string2 = '10as1' cfoutput#compareNoCase(replace(string1,'^0+|0$','','all'),rereplace(string2,'^0+|0$','','all'))#/cfoutput You're doing a replace() on string1. Not a rereplace(). -- I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321403 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare Strings
Your first set of statements will work once you change the first replace to rereplace. - Rod On Tue, Apr 7, 2009 at 9:53 AM, Pranathi Reddy rk.prana...@gmail.comwrote: cfset string1 = '0010as1000' cfset string2 = '10as1' cfoutput#compareNoCase(replace(string1,'^0*|0*$','','all'),rereplace(string2,'^0*|0*$','','all'))#/cfoutput ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321404 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare Strings
Thank alot It worked... Pranathi. I'm still learning regex, so i'm less concerned with inefficient and more concerned with holy crap it worked :) I had the + in place of the * initially, but read something that made me think the * would be better in that case. I probably misunderstood (and don't recall what it was specifically, as I read it late last night and I'm typing this early the next morning), but I think I get what you're saying. The + matches 1 or more, whereas the * matches zero or more... so with the asterisk the engine always hits the beginning and end (even in the case of zero zeros). Thanks. Just woke up and learned something. I can go back to bed :) -- I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321406 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare FieldValues with DataBase Values
I appologise if I really confused you and I am still learning the Coldfusion and I am unable to code in a proper way. These are my ColdFuion Fields in the Coldfusion page: FirstName, LastName,Address,PhoneNumber, EmployerID, DepartmentID , DepartmentName, EmployerID, Branch Sql Database Tables: Table1: Employer FirstName, LastName,Address,PhoneNumber, EmployerID (Primary Key) Daniel RayNJ 123-123-2123 1234 Ria ParksNj 124-123-1234 1221 EmloyerID is the Primary key. Table2: Department DepartmentID , DepartmentName, EmployerID, Branch, SNO(Unique Identifier) 12 Economics 1234NJ 111 13 Physics1234NJ 121 12 Economics 1221PA 123 EmployerID can be repeated but not SNO should be unique. I need to do two things. 1. Depending on the entry of the formfield values. The database tables should be either inserted or updated. If the row already exsist then the row should be inserted and if exsist row should be updated. eg: I need to add the Chemistry DepartmentNAme to the EmployerID 1234. I need to check before the insert statement whether or not the data in the table. How do I really check this. 2. Once the action takes place either update or delete, I need to display the list of the action taken place in the user session on the coldfusion page. Session is just the user session here. eg: DepartmentName for 1234 is insesrted Salary for 1221 has been updated . . . Hope I am very clear now... Thanks, Priya I have an issue and I am out of my mind.. to solve it. FieldNames: FirstName, LastName,Address,PhoneNumber, EmployerID, DepartmentID , DepartmentName, EmployerID, Branch Ok, what about these field names? Can you explain for us please? Are these columns in a table? Columns in a result set? Do you realize you have EmployerID in there twice?? Table Employee FirstName, LastName,Address,PhoneNumber, EmployerID Is EmployerID the primary key or a foreign key? I would have expected it to be called EmployeeID if it were the primary key, since an employer employs the employee. Table Department DepartmentID , DepartmentName, EmployerID, Branch I have a FirstName attribute in the table and I want to compare these attribute (FirstName) with the fieldvalue. What field value?? Is this in a ColdFusion page or a SQL statement? Where is the fieldvalue coming from? A form? The Url? In what way do you want to compare? Do you want to compare a single record, or more than one? ? I thought of using arrays but I don't know how to add database date to the array and compare. What is a database date? None of the columns you have mentioned so far seem to have anything to do with dates. Setting a date into an array is easy as cfset myArray = [now()] but I'm not sure where you are going with this. I also have one more issue. I am trying to insert data into the database and once the data is inserted the inserted (FirstName) should be displayed (Primary Key is EmployerID). What table is this? The employee table? If EmployerID is the primary key of the Employee table, why is that column also in the Department table? If two people are in the same department, do you have two department records with the same department name, but different EmployerIds? I'm quite confused. If I add n number of FirstNames in the table in the session then it should display those many New entry values in the main page. How can we do it? Is the table in session, or do you simply mean inserts into the database during a user's session? So you want to have a screen that shows someone the records they entered since their session started? You could store the data in a session variable, or store the primary key of the records they inserted. I'm not sure where you are headed, but this would also be possible if you had a userIDModified in the table and a datetimeCreated column. You could simply query out the records that user had inserted since the time their session started. Can anyone help me with this? Probably, but we need a bit more direction on what it is you are doing. I'm pretty bad at guessing. ~Brad ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320259 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare FieldValues with DataBase Values
one sure-fire way to check if the data exists in a table is to run a SELECT query to select that data. then check the recordcount of the select query - if it is 0 then no such data exists; otherwise the data is already there. depending on your db and exact requirements you may be able to use db-specific ways to do it. i.e. MySQL has a ON DUPLICATE KEY UPDATE clause one can use in an INSERT query which will update rows instead of inserting new rows if specified PK value already exists in a table. but since you seem to need to notify user of exact action taken (insert or update) that may not be useful in your case... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Priya Koya wrote: I appologise if I really confused you and I am still learning the Coldfusion and I am unable to code in a proper way. These are my ColdFuion Fields in the Coldfusion page: FirstName, LastName,Address,PhoneNumber, EmployerID, DepartmentID , DepartmentName, EmployerID, Branch Sql Database Tables: Table1: Employer FirstName, LastName,Address,PhoneNumber, EmployerID (Primary Key) Daniel RayNJ 123-123-2123 1234 Ria ParksNj 124-123-1234 1221 EmloyerID is the Primary key. Table2: Department DepartmentID , DepartmentName, EmployerID, Branch, SNO(Unique Identifier) 12 Economics 1234NJ 111 13 Physics1234NJ 121 12 Economics 1221PA 123 EmployerID can be repeated but not SNO should be unique. I need to do two things. 1. Depending on the entry of the formfield values. The database tables should be either inserted or updated. If the row already exsist then the row should be inserted and if exsist row should be updated. eg: I need to add the Chemistry DepartmentNAme to the EmployerID 1234. I need to check before the insert statement whether or not the data in the table. How do I really check this. 2. Once the action takes place either update or delete, I need to display the list of the action taken place in the user session on the coldfusion page. Session is just the user session here. eg: DepartmentName for 1234 is insesrted Salary for 1221 has been updated . . . Hope I am very clear now... Thanks, Priya ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320262 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare FieldValues with DataBase Values
Yes, I can use select statement but I am able to display only the latest taction taken to the coldfusion page but not all the list of actions taken in a user session. On Mon, Mar 9, 2009 at 9:40 AM, Azadi Saryev az...@sabai-dee.com wrote: one sure-fire way to check if the data exists in a table is to run a SELECT query to select that data. then check the recordcount of the select query - if it is 0 then no such data exists; otherwise the data is already there. depending on your db and exact requirements you may be able to use db-specific ways to do it. i.e. MySQL has a ON DUPLICATE KEY UPDATE clause one can use in an INSERT query which will update rows instead of inserting new rows if specified PK value already exists in a table. but since you seem to need to notify user of exact action taken (insert or update) that may not be useful in your case... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Priya Koya wrote: I appologise if I really confused you and I am still learning the Coldfusion and I am unable to code in a proper way. These are my ColdFuion Fields in the Coldfusion page: FirstName, LastName,Address,PhoneNumber, EmployerID, DepartmentID , DepartmentName, EmployerID, Branch Sql Database Tables: Table1: Employer FirstName, LastName,Address,PhoneNumber, EmployerID (Primary Key) Daniel RayNJ 123-123-2123 1234 Ria ParksNj 124-123-1234 1221 EmloyerID is the Primary key. Table2: Department DepartmentID , DepartmentName, EmployerID, Branch, SNO(Unique Identifier) 12 Economics 1234NJ 111 13 Physics1234NJ 121 12 Economics 1221PA 123 EmployerID can be repeated but not SNO should be unique. I need to do two things. 1. Depending on the entry of the formfield values. The database tables should be either inserted or updated. If the row already exsist then the row should be inserted and if exsist row should be updated. eg: I need to add the Chemistry DepartmentNAme to the EmployerID 1234. I need to check before the insert statement whether or not the data in the table. How do I really check this. 2. Once the action takes place either update or delete, I need to display the list of the action taken place in the user session on the coldfusion page. Session is just the user session here. eg: DepartmentName for 1234 is insesrted Salary for 1221 has been updated . . . Hope I am very clear now... Thanks, Priya ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320264 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare FieldValues with DataBase Values
that's because you are probably over-writing some variable you created to hold the action with each subsequent action... use an array or structure to hold all your action, append each new action taken to this array/structure, then loop over it to display all actions taken Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Priya Koya wrote: Yes, I can use select statement but I am able to display only the latest taction taken to the coldfusion page but not all the list of actions taken in a user session. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320276 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare FieldValues with DataBase Values
Yes, that was much clearer. Thank you :) Are you using the cfapplication tag in your application? Do you have session management enabled? If so, you can keep a list of the user's operations in a session variable. cfset session.array_of_user_actions = arrayNew(1) cfset arrayAppend(session.array_of_user_actions,User Inserted Record A) cfset arrayAppend(session.array_of_user_actions,User Updated Record B) cfset arrayAppend(session.array_of_user_actions,User Scratched His Head) This would create an array that you could append to as you saw fit with the user's actions. I don't neccessarily think it's the best way to audit your users actions, but it would work. Session variables do not get deleted at the end of your page request. They will be available on each subsequent request from the same user providing his session has not timed out. h3User's Activity During This Session/h3 cfloop array=#session.array_of_user_actions# index=this_action #this_action#br /cfloop As far as knowing whether you are inserting or updating-- Generally when a user is updating a record, he selects it from some list and you populate the values for it into a form. At that point simply place the primary key from that table in a hidden field in the form. When you submit the form, the processing page can look for the existence of that hidden form field. If the primary key was passed, it's an update, otherwise it's an insert. ~Brad - Original Message - From: Priya Koya priya23...@gmail.com To: cf-talk cf-talk@houseoffusion.com Sent: Monday, March 09, 2009 9:49 AM Subject: Re: Compare FieldValues with DataBase Values Yes, I can use select statement but I am able to display only the latest taction taken to the coldfusion page but not all the list of actions taken in a user session. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320278 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare FieldValues with DataBase Values
ohh yes.. Thanks a lot. On Mon, Mar 9, 2009 at 10:56 AM, Azadi Saryev az...@sabai-dee.com wrote: that's because you are probably over-writing some variable you created to hold the action with each subsequent action... use an array or structure to hold all your action, append each new action taken to this array/structure, then loop over it to display all actions taken Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Priya Koya wrote: Yes, I can use select statement but I am able to display only the latest taction taken to the coldfusion page but not all the list of actions taken in a user session. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320283 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare FieldValues with DataBase Values
Any ideas? Hi All, I have an issue and I am out of my mind.. to solve it. FieldNames: FirstName, LastName,Address,PhoneNumber, EmployerID, DepartmentID , DepartmentName, EmployerID, Branch Table Employee FirstName, LastName,Address,PhoneNumber, EmployerID Table Department DepartmentID , DepartmentName, EmployerID, Branch I have a FirstName attribute in the table and I want to compare these attribute (FirstName) with the fieldvalue. I thought of using arrays but I dont know how to add database date to the array and compare. I also have one more issue. I am trying to insert data into the database and once the data is inserted the inserted (FirstName) should be displayed (Primary Key is EmployerID). If I add n number of FirstNames in the table in the session then it should display those many New entry values in the main page. How can we do it? Can anyone help me with this? Thanks in Advance... Priya ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320252 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare FieldValues with DataBase Values
- Original Message - From: Priya Koya priya23...@gmail.com To: cf-talk cf-talk@houseoffusion.com I have an issue and I am out of my mind.. to solve it. FieldNames: FirstName, LastName,Address,PhoneNumber, EmployerID, DepartmentID , DepartmentName, EmployerID, Branch Ok, what about these field names? Can you explain for us please? Are these columns in a table? Columns in a result set? Do you realize you have EmployerID in there twice?? Table Employee FirstName, LastName,Address,PhoneNumber, EmployerID Is EmployerID the primary key or a foreign key? I would have expected it to be called EmployeeID if it were the primary key, since an employer employs the employee. Table Department DepartmentID , DepartmentName, EmployerID, Branch I have a FirstName attribute in the table and I want to compare these attribute (FirstName) with the fieldvalue. What field value?? Is this in a ColdFusion page or a SQL statement? Where is the fieldvalue coming from? A form? The Url? In what way do you want to compare? Do you want to compare a single record, or more than one? ? I thought of using arrays but I don't know how to add database date to the array and compare. What is a database date? None of the columns you have mentioned so far seem to have anything to do with dates. Setting a date into an array is easy as cfset myArray = [now()] but I'm not sure where you are going with this. I also have one more issue. I am trying to insert data into the database and once the data is inserted the inserted (FirstName) should be displayed (Primary Key is EmployerID). What table is this? The employee table? If EmployerID is the primary key of the Employee table, why is that column also in the Department table? If two people are in the same department, do you have two department records with the same department name, but different EmployerIds? I'm quite confused. If I add n number of FirstNames in the table in the session then it should display those many New entry values in the main page. How can we do it? Is the table in session, or do you simply mean inserts into the database during a user's session? So you want to have a screen that shows someone the records they entered since their session started? You could store the data in a session variable, or store the primary key of the records they inserted. I'm not sure where you are headed, but this would also be possible if you had a userIDModified in the table and a datetimeCreated column. You could simply query out the records that user had inserted since the time their session started. Can anyone help me with this? Probably, but we need a bit more direction on what it is you are doing. I'm pretty bad at guessing. ~Brad ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320253 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare digits as strings not integers.
Using compare() and compareNoCase() is really the right way. My only gripe is that the returned value isn't very intuitive. On Mon, Apr 28, 2008 at 5:57 PM, Bobby Hartsfield [EMAIL PROTECTED] wrote: Just stick an alpha character in front of them when comparing or compare len AND value. ..:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Monday, April 28, 2008 4:08 PM To: CF-Talk Subject: Compare digits as strings not integers. CFSET oldcompany = '033' CFSET newcompany = '0033' How would one compare these as strings and get a NOT equal result rather then integers resulting in an equal result? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304390 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare digits as strings not integers.
Well its ONE way... ;-) ..:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Sonny Savage [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 29, 2008 8:12 AM To: CF-Talk Subject: Re: Compare digits as strings not integers. Using compare() and compareNoCase() is really the right way. My only gripe is that the returned value isn't very intuitive. On Mon, Apr 28, 2008 at 5:57 PM, Bobby Hartsfield [EMAIL PROTECTED] wrote: Just stick an alpha character in front of them when comparing or compare len AND value. ..:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Monday, April 28, 2008 4:08 PM To: CF-Talk Subject: Compare digits as strings not integers. CFSET oldcompany = '033' CFSET newcompany = '0033' How would one compare these as strings and get a NOT equal result rather then integers resulting in an equal result? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304397 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare digits as strings not integers.
Ian, this may work for you. Axel CFIF oldcompany IS newcompany AND LEN(oldcompany) IS LEN(newcompany) they are equal cfelse they are not equal /CFIF CFSET oldcompany = '033' CFSET newcompany = '0033' How would one compare these as strings and get a NOT equal result rather then integers resulting in an equal result? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304410 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare digits as strings not integers.
Ian, this may work for you. Axel CFIF oldcompany IS newcompany AND LEN(oldcompany) IS LEN(newcompany) they are equal cfelse they are not equal /CFIF CFSET oldcompany = '033' CFSET newcompany = '0033' How would one compare these as strings and get a NOT equal result rather then integers resulting in an equal result? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304411 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare digits as strings not integers.
CFSET oldcompany = '033' CFSET newcompany = '0033' How would one compare these as strings and get a NOT equal result rather then integers resulting in an equal result? You could tack a non-numeric value to the end of each, and compare the resultant strings. There may be a better, and/or more elegant way, that's just off the top of my head. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304353 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare digits as strings not integers.
Use the Compare() function, ie: CFSET oldcompany = '033' CFSET newcompany = '0033' CFOUTPUT#(oldcompany EQ newcompany)#BR #(Compare(oldcompany, newcompany) EQ 0)# /CFOUTPUT -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304354 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare digits as strings not integers.
Dave Watts wrote: CFSET oldcompany = '033' CFSET newcompany = '0033' How would one compare these as strings and get a NOT equal result rather then integers resulting in an equal result? You could tack a non-numeric value to the end of each, and compare the resultant strings. There may be a better, and/or more elegant way, that's just off the top of my head. I was considering that, but I felt sure there was a CF way to do this, finally found the right documentation page compare(). CFSET aVal = '033' CFSET bVal = '0033' cfoutput #aVal#:#bVal# #aVal EQ bVal# #aVal IS bVal# #compare(aVal,bVal)# /cfoutput ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304355 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare digits as strings not integers.
Just stick an alpha character in front of them when comparing or compare len AND value. ..:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Monday, April 28, 2008 4:08 PM To: CF-Talk Subject: Compare digits as strings not integers. CFSET oldcompany = '033' CFSET newcompany = '0033' How would one compare these as strings and get a NOT equal result rather then integers resulting in an equal result? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304361 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
I don't think you can do a subselect in a QofQ (someone correct me if I'm wrong) so I think the easiest thing would be to pull both in and then walk the arrays. Brute force and inelegant, but is this something you need to do once, or is this something that must be done often? --Ben Doom Ian Skinner wrote: Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294801 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two tables
just thinking out loud here...could you select them both into a CF query struct, then do a q of q on the two result sets to extract whatever data you needed? On Dec 14, 2007 9:57 AM, Ian Skinner [EMAIL PROTECTED] wrote: Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294806 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two tables
Suck the two tables down as WDDX files - work with them locally and use query of queries? On Dec 14, 2007 10:57 AM, Ian Skinner [EMAIL PROTECTED] wrote: Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294799 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two tables
I'd probably make two separate queries, one to each database and then do a query of queries to find the data you're looking for. Koen ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294800 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two tables
QoQ? On Dec 14, 2007 10:57 AM, Ian Skinner [EMAIL PROTECTED] wrote: Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294803 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
Ben Doom wrote: I don't think you can do a subselect in a QofQ (someone correct me if I'm wrong) so I think the easiest thing would be to pull both in and then walk the arrays. Brute force and inelegant, but is this something you need to do once, or is this something that must be done often? --Ben Doom Yeah, this is a case where it would be nice if QofQ had more capability. This would be simple with the ability to do an outer join between the tables. Luckily it only needs to be done once, or at least only a few times until it is determined what is out of sync between the old version (Access) and the new version (SQL) and it is reconciled. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294812 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
If you can pull both result sets into ColdFusion through their respective data sources, you should be able to use a QofQ. A left outer join isn't supported in QofQ's, but there are work-arounds: http://www.bealearts.co.uk/blog/2007/06/20/how-to-do-an-outer-join-in-qu ery-of-queries/ ~Brad -Original Message- From: Ben Doom [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 10:12 AM To: CF-Talk Subject: Re: Compare two tables I don't think you can do a subselect in a QofQ (someone correct me if I'm wrong) so I think the easiest thing would be to pull both in and then walk the arrays. Brute force and inelegant, but is this something you need to do once, or is this something that must be done often? --Ben Doom Ian Skinner wrote: Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294811 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare two tables
Since everyone else mentioned QoQ, another option would be creating a temp table in SQL, loading the Access records, and then doing the query. M!ke -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 9:57 AM To: CF-Talk Subject: Compare two tables Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294817 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare two tables
Are you saying you want to only look at the License Number column? If that's the case, query both tables and then find the missing ones with Q of Q or the following UDF. http://www.cflib.org/udf.cfm?ID=660 Adrian -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: 14 December 2007 15:57 To: CF-Talk Subject: Compare two tables Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294818 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
I had considered the list idea a couple of different ways. I forgot to mention the size of the tables involved. One is ~40,000 records and the other is ~46,000. Are these list ideas going to hold up with lists several tens of thousands of items long? Dominic Watson wrote: If you can get them both into cfquery objs, then try this (untested): (QoQ = Query of Queries as below) cfquery name=inAccessNotInSQLServer dbtype=query SELECT * FROM qry_access WHERE URN NOT IN (#ValueList(qry_sqlServer.URN)#) /cfquery cfquery name=inSQLServerNotInAccess dbtype=query SELECT * FROM qry_sqlServer WHERE URN NOT IN (#ValueList(qry_access.URN)#) /cfquery Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294826 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
That was considered but we do not have authority to upload the Access table into the SQL database, thus the list line of my original post. Dawson, Michael wrote: Since everyone else mentioned QoQ, another option would be creating a temp table in SQL, loading the Access records, and then doing the query. M!ke -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 9:57 AM To: CF-Talk Subject: Compare two tables Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294825 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare two tables
The list stuff should work on that many records -- performance might be suspect though. I'm not sure what exactly was meant by not having the authority to put the data in SQL server. It's more of a technicality, but what if you dynamically loaded the data into a temp table or table variable in a cfquery tag and then do your join to that? Overall it might perform better and you wouldn't actually be putting the data in SQL server :) ~Brad -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 12:02 PM To: CF-Talk Subject: Re: Compare two tables I had considered the list idea a couple of different ways. I forgot to mention the size of the tables involved. One is ~40,000 records and the other is ~46,000. Are these list ideas going to hold up with lists several tens of thousands of items long? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294830 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
Aha! That is the kind of outside the box thinking for which I was looking. Presuming that the SQL DSN user has Create permission (50/50 chance) this could work nicely. I'll pass it on to the co-worker dealing with this issue. He is having trouble with this HOF account, see CF-OT post to that affect. Thank You Ian Adrian Lynch wrote: You wouldn't have to import the Access data into the SQL database, sorry, you started the with the quotes ;O). You can just select the data from Access, loop over it and generate the SQL in insert into a temp table. Something like... cfquery name=theAccessQuery datasource=... QUERY THE ACCESS DATA HERE /cfquery cfquery name=theSQLQuery datasource=... CREATE #temp (COLUMNS GO HERE) cfloop query=theAccessQuery INSERT INTO #temp (COLUMNS GO HERE) VALUES (VALUES GO HERE) /cfloop SELECT FROM THE SQL TABLE HERE /cfquery Why would you do this? Well, you now have all the Access data in SQL Server and have available all the functions and constructs. One that might be interesting is CHECKSUM and BINARY_CHECKSUM which I don't think is available in Access. Interesting post. Adrian -Original Message- From: Todd [mailto:[EMAIL PROTECTED] Sent: 14 December 2007 17:33 To: CF-Talk Subject: Re: Compare two tables we can not import the Access data into the SQL database at this time at least. On Dec 14, 2007 12:11 PM, Dawson, Michael [EMAIL PROTECTED] wrote: Since everyone else mentioned QoQ, another option would be creating a temp table in SQL, loading the Access records, and then doing the query. M!ke -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 9:57 AM To: CF-Talk Subject: Compare two tables Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294829 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
Just a thought, Ian, but you might try this solution versus doing a QoQ with lists versus doing a nested loop to test for matches and see which one actually works better for you performance-wise. If you've got 40k records, that's a lot of data to be shuttling back and forth! On Dec 14, 2007 1:14 PM, Ian Skinner [EMAIL PROTECTED] wrote: Aha! That is the kind of outside the box thinking for which I was looking. Presuming that the SQL DSN user has Create permission (50/50 chance) this could work nicely. I'll pass it on to the co-worker dealing with this issue. He is having trouble with this HOF account, see CF-OT post to that affect. Thank You Ian Adrian Lynch wrote: You wouldn't have to import the Access data into the SQL database, sorry, you started the with the quotes ;O). You can just select the data from Access, loop over it and generate the SQL in insert into a temp table. Something like... cfquery name=theAccessQuery datasource=... QUERY THE ACCESS DATA HERE /cfquery cfquery name=theSQLQuery datasource=... CREATE #temp (COLUMNS GO HERE) cfloop query=theAccessQuery INSERT INTO #temp (COLUMNS GO HERE) VALUES (VALUES GO HERE) /cfloop SELECT FROM THE SQL TABLE HERE /cfquery Why would you do this? Well, you now have all the Access data in SQL Server and have available all the functions and constructs. One that might be interesting is CHECKSUM and BINARY_CHECKSUM which I don't think is available in Access. Interesting post. Adrian -Original Message- From: Todd [mailto:[EMAIL PROTECTED] Sent: 14 December 2007 17:33 To: CF-Talk Subject: Re: Compare two tables we can not import the Access data into the SQL database at this time at least. On Dec 14, 2007 12:11 PM, Dawson, Michael [EMAIL PROTECTED] wrote: Since everyone else mentioned QoQ, another option would be creating a temp table in SQL, loading the Access records, and then doing the query. M!ke -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 9:57 AM To: CF-Talk Subject: Compare two tables Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294832 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
Nope, QofQ does now allow for outer joins either with the left outer syntax, nor with the *= syntax. The work-around is usually to union two selects like in the link I posted earlier. ~Brad -Original Message- From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 12:03 PM To: CF-Talk Subject: Re: Compare two tables You can't do sub-selects but I'm pretty sure you can do outer joins... SELECT A.ID, A.LicenseNumber, B.ID, B.LicenseNumber FROM qOne A LEFT OUTER JOIN qTwo B ON A.LicenseNumber = B.LicenseNumber WHERE B.ID IS NULL That *should* return all of the records in qOne that don't have a match in qTwo... work with it from there for the rest ;) Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294833 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
Yeah, this is a case where it would be nice if QofQ had more capability. This would be simple with the ability to do an outer join between the tables. Luckily it only needs to be done once, or at least only a few times until it is determined what is out of sync between the old version (Access) and the new version (SQL) and it is reconciled. You can't do sub-selects but I'm pretty sure you can do outer joins... SELECT A.ID, A.LicenseNumber, B.ID, B.LicenseNumber FROM qOne A LEFT OUTER JOIN qTwo B ON A.LicenseNumber = B.LicenseNumber WHERE B.ID IS NULL That *should* return all of the records in qOne that don't have a match in qTwo... work with it from there for the rest ;) Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294827 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare two tables
You wouldn't have to import the Access data into the SQL database, sorry, you started the with the quotes ;O). You can just select the data from Access, loop over it and generate the SQL in insert into a temp table. Something like... cfquery name=theAccessQuery datasource=... QUERY THE ACCESS DATA HERE /cfquery cfquery name=theSQLQuery datasource=... CREATE #temp (COLUMNS GO HERE) cfloop query=theAccessQuery INSERT INTO #temp (COLUMNS GO HERE) VALUES (VALUES GO HERE) /cfloop SELECT FROM THE SQL TABLE HERE /cfquery Why would you do this? Well, you now have all the Access data in SQL Server and have available all the functions and constructs. One that might be interesting is CHECKSUM and BINARY_CHECKSUM which I don't think is available in Access. Interesting post. Adrian -Original Message- From: Todd [mailto:[EMAIL PROTECTED] Sent: 14 December 2007 17:33 To: CF-Talk Subject: Re: Compare two tables we can not import the Access data into the SQL database at this time at least. On Dec 14, 2007 12:11 PM, Dawson, Michael [EMAIL PROTECTED] wrote: Since everyone else mentioned QoQ, another option would be creating a temp table in SQL, loading the Access records, and then doing the query. M!ke -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 9:57 AM To: CF-Talk Subject: Compare two tables Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294823 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
If you can get them both into cfquery objs, then try this (untested): (QoQ = Query of Queries as below) cfquery name=inAccessNotInSQLServer dbtype=query SELECT * FROM qry_access WHERE URN NOT IN (#ValueList(qry_sqlServer.URN)#) /cfquery cfquery name=inSQLServerNotInAccess dbtype=query SELECT * FROM qry_sqlServer WHERE URN NOT IN (#ValueList(qry_access.URN)#) /cfquery Dominic ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294815 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
we can not import the Access data into the SQL database at this time at least. On Dec 14, 2007 12:11 PM, Dawson, Michael [EMAIL PROTECTED] wrote: Since everyone else mentioned QoQ, another option would be creating a temp table in SQL, loading the Access records, and then doing the query. M!ke -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 9:57 AM To: CF-Talk Subject: Compare two tables Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294819 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two tables
Scratch that idea - I just re-read the quickdocs and QoQ doesn't support joins at all. (*grumble*) Just saw where you found a possible solution... yay! On Dec 14, 2007 1:03 PM, C. Hatton Humphrey [EMAIL PROTECTED] wrote: Yeah, this is a case where it would be nice if QofQ had more capability. This would be simple with the ability to do an outer join between the tables. Luckily it only needs to be done once, or at least only a few times until it is determined what is out of sync between the old version (Access) and the new version (SQL) and it is reconciled. You can't do sub-selects but I'm pretty sure you can do outer joins... SELECT A.ID, A.LicenseNumber, B.ID, B.LicenseNumber FROM qOne A LEFT OUTER JOIN qTwo B ON A.LicenseNumber = B.LicenseNumber WHERE B.ID IS NULL That *should* return all of the records in qOne that don't have a match in qTwo... work with it from there for the rest ;) Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294831 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
This is one of a couple of things which was much easier to do in primitive databases like dBase or Clipper. But I would use an equivalent technique here, just looping in both queries in the same time. (not tested) CFQUERY NAME=q1 DATASOURCE=DS1 SELECT field ORDER BY field /CFQUERY CFQUERY NAME=q2 DATASOURCE=DS2 SELECT field ORDER BY field /CFQUERY CFSET index1 = 1 CFSET index2 = 1 CFLOOP CONDITION=index1 LTE DS1.recordCount OR index2 LTE DS2.recordCount CFIF index2 GT DS2.recordCount OR q2.field[index2] GT q1.field[index1] !--- q1.field[index1] is a record in DS1 which is not in DS2: do whatever you have to do with it, then skip to next record in DS1 --- CFSET index1 = index1 + 1 CFELSEIF index1 GT DS1.recordCount OR q1.field[index1] GT q2.field[index2] !--- q2.field[index2] is a record in DS2 which is not in DS1: do whatever you have to do with it, then skip to next record in DS2 --- CFSET index2 = index2 + 1 CFELSE !--- both records are the same: skip to next in both DS --- CFSET index1 = index1 + 1 CFSET index2 = index2 + 1 /CFIF /CFLOOP -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294838 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
Pardon me for correcting Brad: QofQ does NOT allow for outer joins I'm glad I'm not the only one that mistypes one letter which changes the entire meaning of a sentence. :-D M!ke -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 12:57 PM To: CF-Talk Subject: RE: Compare two tables Nope, QofQ does now allow for outer joins either with the left outer syntax, nor with the *= syntax. The work-around is usually to union two selects like in the link I posted earlier. ~Brad ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294842 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
Lol. That does make a difference, doesn't it. :) ~Brad -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 2:07 PM To: CF-Talk Subject: RE: Compare two tables Pardon me for correcting Brad: QofQ does NOT allow for outer joins I'm glad I'm not the only one that mistypes one letter which changes the entire meaning of a sentence. :-D M!ke ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294844 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
I'm not talking about an import. I'm talking about a temp table that will last the duration of the connection. M!ke -Original Message- From: Todd [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 11:33 AM To: CF-Talk Subject: Re: Compare two tables we can not import the Access data into the SQL database at this time at least. On Dec 14, 2007 12:11 PM, Dawson, Michael [EMAIL PROTECTED] wrote: Since everyone else mentioned QoQ, another option would be creating a temp table in SQL, loading the Access records, and then doing the query. M!ke -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 9:57 AM To: CF-Talk Subject: Compare two tables Anybody have a creative way to compare data in two tables and find all records in table A not in table B and vice-a-versa. The data share a common key, 'License Number'. The trick -- Table A is in an MS Access Database and Table B is in a MS SQL server database. They are not aware of each other and we can not import the Access data into the SQL database at this time at least. TIA Ian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294840 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
That is the kind of outside the box thinking for which I was looking You are welcome! ;-) Adrian, thanks for having my back and writing the sample code. M!ke Adrian Lynch wrote: You wouldn't have to import the Access data into the SQL database, sorry, you started the with the quotes ;O). You can just select the data from Access, loop over it and generate the SQL in insert into a temp table. Something like... cfquery name=theAccessQuery datasource=... QUERY THE ACCESS DATA HERE /cfquery cfquery name=theSQLQuery datasource=... CREATE #temp (COLUMNS GO HERE) cfloop query=theAccessQuery INSERT INTO #temp (COLUMNS GO HERE) VALUES (VALUES GO HERE) /cfloop SELECT FROM THE SQL TABLE HERE /cfquery Why would you do this? Well, you now have all the Access data in SQL Server and have available all the functions and constructs. One that might be interesting is CHECKSUM and BINARY_CHECKSUM which I don't think is available in Access. Interesting post. Adrian ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294841 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare two lists and if one element common ...
Use ListToArray, then use the built in .contains() method from Java. [SNIP] 3. Search : I have heard people complaining that there is no find method in Array. Actually you had it all the time. Just that it was hidden :) You can use List.Contains() or List.indexOf() methods to achieve that. Here is the code. cfset y = ListToArray(rupesh,tom,damon,hemant,ashwin,ram,prank,sanjeev)cfoutputCo ntains Hemant: #y.contains(hemant)#/cfoutputcfoutputIndex of damon : #y.indexof(damon)#/cfoutput [/SNIP] http://coldfused.blogspot.com/2007/01/extend-cf-native-objects-harnessing.ht ml -Original Message- From: Mike Kear [mailto:[EMAIL PROTECTED] Sent: Sunday, April 15, 2007 2:20 AM To: CF-Talk Subject: Compare two lists and if one element common ... Is there an easy way to compare two lists and if there is at least one common value then .. do something? What I'm imagining is something like this: UserPermissions = 1000,1003,1005,1006,1007 (held in the session.user.cfc after login) Permissions required = 1003,6048,6484,7809 (part of the page parameters from the CMS) And this user has permission 1003 and the page requirements include 1003 so he's allowed here.(on the page, the permissions required would be OR not AND) ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275334 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
OUTCOME: Re: Compare two lists and if one element common ... -
Thanks Jim for your suggestions. Its a bit late in the day for this project to redesign the whole user authentication/access/permission control architecture,but i have another project starting in a couple of weeks and ill take a good look at what you've done for that one. I am grateful for you input. I've valued your input many times before. Thanks. Thanks too, to Sean for seeing what i already knew and had only put here as a deliberate lie to check if anyone's paying attention g of course my code would only find the last test instead of stopping the first time it found a match! I knew that!! g So here's what i've done. ... In each page of the admin i have the following, right up at the top of the page: cfset RequiredPermissions = 10008,10009 / (meaning anyone holding either of these permissions can access the page - might be an edit content page or a delete a section) Then below that, in the header include is the following: cfparam name=RequiredPermissions default=1 / (1 is the default - everyone has that even anonymous users. so to grant full access of a page to anyone i just dont set a permission in the lines above the header include) cfset allow= application.useraccess.CheckPermissions( session.user.getPermissions(), RequiredPermissions ) / cfif not(allow) cflocation addtoken=no url=/index.cfm / cfabort /cfif The function application.useraccess.CheckPermissions() returns either zero (dont allow access - cant find any permissions match) or a positive integer, being the position of the first match, in which case the user goes on to see the page. Here's the method application.useraccess.CheckPermissions(): cffunction name=CheckPermissions access=public output=false returntype=boolean hint=compares the list of the user's permissions with the permissions required. Returns zero if there are no matches, an integer if at least one of the user's permissions matches one in the required set cfargument name=argUsersList required=yes type=string / cfargument name=argRequiredList required=yes type=string / cfset var OkToGo = false / cfset var Userpermissions = trim(arguments.argUsersList) / cfset var Permissionsrequired = trim(arguments.argRequiredList) / ![ Loop through the list of permissions required, and stop if it finds one ] cfloop list = #Permissionsrequired# index=i cfset OkToGo = listfind(Userpermissions, #i#) / cfif OkToGo cfbreak/ /cfif /cfloop cfreturn OkToGo / /cffunction This will also allow me to use this same method to show content only to some people if i want. I.e. if you're logged in and you're one of the people authorised to see this .. here's a bit just for you: cfif application.useraccess.CheckPermissions( session.user.getPermissions(), 10685 ) pyada yada yada /p /cfif An example might be a page about the organisation's coming events, with lots of stuff for the public, but also a section only shown to logged-in members. (the permission numbers are just numbers i made up for this thread - they're a whole different series of numbers in the real site) Thanks everyone. Might not be the perfect permissions set-up but it's working and it gives me the granular kind of control that i need for this application. And provides for the whims and fancies of the management in the future. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275500 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two lists and if one element common ...
Mike Kear, He who is an Adobe Certified Advanced Coldfusion Devleoper, doesn't know about IsUserInRole(userPermissions,admin,contentAuthor); hehe On 4/15/07, Mike Kear [EMAIL PROTECTED] wrote: Is there an easy way to compare two lists and if there is at least one common value then .. do something? What I'm imagining is something like this: UserPermissions = 1000,1003,1005,1006,1007 (held in the session.user.cfc after login) Permissions required = 1003,6048,6484,7809 (part of the page parameters from the CMS) And this user has permission 1003 and the page requirements include 1003 so he's allowed here.(on the page, the permissions required would be OR not AND) I could do it by looping through one list looking for that value in the other list, but is there a simpler way? In this site, which is an admin area, the users would carry their set of permissons with them as they navigate around gaining access to the pages that accept people with certain permisson values. A bit like a keyring. For example Update content Type 2 might require permission 8065 and Delete content type 2 might require permission 8066 or 8065 I've done it this way because this site has lots of different people working on the content of the site, some have authorisation to (for example) add pages to the site but only the parts of the site that relate to their unit. Other people can add pages too, using the same forms, but only add pages to their own section of the site.In addition, there are levels of authority based on teh amount of training they've had and some other criteria as well. I need it to be a very granular permissions system, so that each user can be granted or denied access to any function independently of any other user. That rules out the more normal way of doing it - assigning levels - contributor, editor, site manager etc.A person might have the level of editor in one area of the site but only basic access to the CMS in other areas of the site. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275249 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two lists and if one element common ...
Yes i do know about IsUserInRole, but as i explained in my question i need a much more granular kind of permission set. And anyway i'm not using the CFLOGIN system for a variety of reasons. So yes i do know about it, but in this case I can't use it. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On 4/15/07, Andrew Scott [EMAIL PROTECTED] wrote: Mike Kear, He who is an Adobe Certified Advanced Coldfusion Devleoper, doesn't know about IsUserInRole(userPermissions,admin,contentAuthor); hehe ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275251 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two lists and if one element common ...
Mike, thats the only method in CF that is the simplest without doing your own IsUserInRole()... On 4/15/07, Mike Kear [EMAIL PROTECTED] wrote: Yes i do know about IsUserInRole, but as i explained in my question i need a much more granular kind of permission set. And anyway i'm not using the CFLOGIN system for a variety of reasons. So yes i do know about it, but in this case I can't use it. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On 4/15/07, Andrew Scott [EMAIL PROTECTED] wrote: Mike Kear, He who is an Adobe Certified Advanced Coldfusion Devleoper, doesn't know about IsUserInRole(userPermissions,admin,contentAuthor); hehe ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275252 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two lists and if one element common ...
I have done my own. There are lots of reasons why the cflogin system was not appropriate for this site, but most was that the CFLOGIN is too one-size-fits-all for the organisation. I have already built the security on this site - it's been in place for 18 months now but i need to improve the way we contorl who does what. Now each page, each part of the site will have a set of authorisations attached to it and only people with those permissions will be able to do those functions for that page. But anyway that wasnt my question. I'm not going to debate architecture decisions that were made ages ago and changing them would require a LOT of rewriting. Honestly, I do wish when someone asks a question on this list like How do I fix the problem with my Ford's front wheel? there wouldnt be a whole host of answers like you should be driving a BMW. or you should have bought a green car instead. My question was about how to compare two lists, and see if there is at least one common value. A way other than looping through one list and looking for each value in turn in the other list that is. Is there are more elegant way to do it than something like : cfset UserPermissions = 1000,1003,1005,1006,1007 / (from the user's login details held in the user.cfc) cfset Permissionsrequired = 1003,6048,6484,7809 / (held in the page header for that page - a dynamic list compiled as the page is called) cfset OkToGo = false / cfloop list = #UserPermissions# index=i cfset OkToGo = listfind(Permissionsrequired, #i#) / /cfloop cfif OkToGo is false cflocation addtoken=no url=/index.cfm / cfabort /cfif This all seems a bit clumsy to me but it will work. What i dont like about it is that it'll loop through all of hte user's permisson values, even if it finds a mark on the first one. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On 4/15/07, Andrew Scott [EMAIL PROTECTED] wrote: Mike, thats the only method in CF that is the simplest without doing your own IsUserInRole()... ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275253 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two lists and if one element common ...
Mike Kear wrote: Is there an easy way to compare two lists and if there is at least one common value then .. do something? cflib has a listcompare function. Jochem ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275254 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
