RE: Empty list for an IN clause
Ok, this seems to be working. RDT_MOBLID IN (cfqueryparam value=#listAppend(valueList(Safetrace.R_MDL_MOBLID),'0')# list=yes cfsqltype=cf_sql_varchar) AND -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning -Original Message- From: Ian Skinner Sent: Monday, March 28, 2005 9:20 AM To: CF-Talk Subject: Empty list for an IN clause What is the simplest way to handle the possibility of an empty list for an IN clause. For Example: RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200213 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Empty list for an IN clause
in your case, perhaps this would suffice cfif safetrace.recordcount GT 0 RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) /cfif D On Mon, 28 Mar 2005 09:19:35 -0800, Ian Skinner [EMAIL PROTECTED] wrote: What is the simplest way to handle the possibility of an empty list for an IN clause. For Example: RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200215 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Empty list for an IN clause
Put the whole clause in a cfif statement: cfif Safetrace.recordCount RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) /cfif Sam On Mon, 28 Mar 2005 09:19:35 -0800, Ian Skinner [EMAIL PROTECTED] wrote: What is the simplest way to handle the possibility of an empty list for an IN clause. For Example: RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200216 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Empty list for an IN clause
Add null=#yesnoformat(NOT listlen(valuelist(Safetrace.R_MDL_MOBLID)))# RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar null=#yesnoformat(NOT listlen(valuelist(Safetrace.R_MDL_MOBLID)))#) Ian Skinner wrote: Ok, this seems to be working. RDT_MOBLID IN (cfqueryparam value=#listAppend(valueList(Safetrace.R_MDL_MOBLID),'0')# list=yes cfsqltype=cf_sql_varchar) AND -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning -Original Message- From: Ian Skinner Sent: Monday, March 28, 2005 9:20 AM To: CF-Talk Subject: Empty list for an IN clause What is the simplest way to handle the possibility of an empty list for an IN clause. For Example: RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200218 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Empty list for an IN clause
That will work fine but if there is an empty list you are asking the database to search for a clause that you know it will not find. Depending on your set up this could cause a performance hit. Sam On Mon, 28 Mar 2005 09:27:27 -0800, Ian Skinner [EMAIL PROTECTED] wrote: Ok, this seems to be working. RDT_MOBLID IN (cfqueryparam value=#listAppend(valueList(Safetrace.R_MDL_MOBLID),'0')# list=yes cfsqltype=cf_sql_varchar) AND -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning -Original Message- From: Ian Skinner Sent: Monday, March 28, 2005 9:20 AM To: CF-Talk Subject: Empty list for an IN clause What is the simplest way to handle the possibility of an empty list for an IN clause. For Example: RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200219 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Empty list for an IN clause
Tried that, it still threw an error, since IN (NULL) I believe is not valid SQL syntax. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning -Original Message- From: Scott Kellogg [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 9:44 AM To: CF-Talk Subject: Re: Empty list for an IN clause Add null=#yesnoformat(NOT listlen(valuelist(Safetrace.R_MDL_MOBLID)))# RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar null=#yesnoformat(NOT listlen(valuelist(Safetrace.R_MDL_MOBLID)))#) Ian Skinner wrote: Ok, this seems to be working. RDT_MOBLID IN (cfqueryparam value=#listAppend(valueList(Safetrace.R_MDL_MOBLID),'0')# list=yes cfsqltype=cf_sql_varchar) AND -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning -Original Message- From: Ian Skinner Sent: Monday, March 28, 2005 9:20 AM To: CF-Talk Subject: Empty list for an IN clause What is the simplest way to handle the possibility of an empty list for an IN clause. For Example: RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200220 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Empty list for an IN clause
I thought of that, but I need the query to return no records, and because of other statements in the where clause, this would cause the query to return all records. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning -Original Message- From: Sam Farmer [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 9:37 AM To: CF-Talk Subject: Re: Empty list for an IN clause Put the whole clause in a cfif statement: cfif Safetrace.recordCount RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) /cfif Sam On Mon, 28 Mar 2005 09:19:35 -0800, Ian Skinner [EMAIL PROTECTED] wrote: What is the simplest way to handle the possibility of an empty list for an IN clause. For Example: RDT_MOBLID IN (cfqueryparam value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes cfsqltype=cf_sql_varchar) -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200221 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Empty list for an IN clause
try this:
cfset mdlList = valueList (Safetrace.R_MDL_MOBLID)
cfif listLen (mdlList) gt 0
run your normal query
cfelse
select * from your table
where 0 = 1
/cfif
Marius Milosav
www.scorpiosoft.com
It's not about Technology, it's about people
Virtual Company (VICO) Application demo
www.scorpiosoft.com/vicodemo/login.cfm
- Original Message -
From: Ian Skinner [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Monday, March 28, 2005 12:51 PM
Subject: RE: Empty list for an IN clause
I thought of that, but I need the query to return no records, and because
of other statements in the where clause, this would cause the query to
return all records.
--
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
C code. C code run. Run code run. Please!
- Cynthia Dunning
-Original Message-
From: Sam Farmer [mailto:[EMAIL PROTECTED]
Sent: Monday, March 28, 2005 9:37 AM
To: CF-Talk
Subject: Re: Empty list for an IN clause
Put the whole clause in a cfif statement:
cfif Safetrace.recordCount
RDT_MOBLID IN (cfqueryparam
value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes
cfsqltype=cf_sql_varchar)
/cfif
Sam
On Mon, 28 Mar 2005 09:19:35 -0800, Ian Skinner
[EMAIL PROTECTED] wrote:
What is the simplest way to handle the possibility of an empty list
for
an IN clause.
For Example:
RDT_MOBLID IN (cfqueryparam
value=#valueList(Safetrace.R_MDL_MOBLID)# list=yes
cfsqltype=cf_sql_varchar)
--
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
C code. C code run. Run code run. Please!
- Cynthia Dunning
Confidentiality Notice: This message including any
attachments is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or
distribution is prohibited. If you are not the
intended recipient, please contact the sender and
delete any copies of this message.
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200224
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
