ColdFusion Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsProgramming Languages - MoreColdFusion Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!
  #1  
Old December 29th, 2004, 01:52 PM
Shmoo Shmoo is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Posts: 38 Shmoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 43 m 17 sec
Reputation Power: 4
Is it possible to loop through multiple queries?

Let's say I have an array of 10 user-submitted case numbers. I need to check each of those case numbers against a table to make sure they're actually valid entries. I do that with this:
Code:
<CFOUTPUT>
<CFLOOP INDEX="i" FROM="1" TO="#ArrayLen(aCaseNumbers)#">
<CFQUERY NAME="checkcase#i#" DATASOURCE="dsource">
     SELECT case_nbr
     FROM casetable
     WHERE case_nbr = '#aCaseNumbers[i]#'
</CFQUERY>
</CFLOOP>
</CFOUTPUT>
This works fine and dandy. It runs 10 seperate queries, named checkase1 through checkcase10. My problem is looping through those 10 query results. If the record count for one is zero, I know the casenumber is bad, and if it's not zero, a record came up, so I can flag the case number as being good. Here's what I'm trying:
Code:
<CFOUTPUT>
<CFLOOP INDEX="i" FROM="1" TO="#ArrayLen(aCaseNumbers)#">
#aCaseNumbers[i]# -- <CFIF #checkcase[i].RecordCount# IS "0">Bad<CFELSE>Good</CFIF><BR>
</CFLOOP>
</CFOUTPUT>
Like I said, when I try looping through those 10 queries, I get errors (Error resolving parameter CHECKCASE for that example), and I don't know if it's my syntax (I've tried a bunch of different things), if it's just not possible, or if I'm just missing something. Is there an easier way to do what I'm trying (for example, running one query for the first part instead of 10 seperate ones), or am I doing it right...except wrong?

Any help/advice would be appreciated, thanks.

Reply With Quote
  #2  
Old December 29th, 2004, 03:24 PM
kiteless kiteless is offline
Moderator
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Jun 2002
Location: Raleigh, NC
Posts: 3,627 kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Week 4 Days 10 h 8 m 55 sec
Reputation Power: 53
You could use an IN operator to run a single query that returns the case numbers that do already exist. Then you'd have to loop through the query and determine which elements in the array are not in the query to figure out which ones *aren't* in the database. Something like this

Code:
<cfscript>
caseNumbers = arrayNew(1);
caseNumbers[1] = '3';
caseNumbers[2] = '8';
caseNumbers[3] = '10';
caseNumbers[4] = '49';
caseNumbers[5] = '55';
caseNumbers[6] = '24';
caseNumbers[7] = '99';
caseNumbers[8] = '106';
</cfscript>
<cfdump var="#caseNumbers#" label="Pretend these were the original case numbers we want to check for.">

<CFQUERY NAME="caseNumbersExist" DATASOURCE="dsource">
     SELECT case_nbr as caseNumber
     FROM casetable
     WHERE case_nbr IN ( #quotedValueList( arrayToList( caseNumbers ) )# )
</CFQUERY>
<cfdump var="#caseNumbersExist#" label="These were found in the database">

<cfset notInDatabase = arrayNew(1) />
<cfloop from="1" to="#arrayLen( caseNumbers )#" index="thisCaseNo">
	<cfset currentCaseNumber = caseNumbers[thisCaseNo] />
	<cfset matchFlag = false />
	<cfloop query="caseNumbersExist">
		<cfif caseNumbersExist.caseNumber eq currentCaseNumber>
			<cfset matchFlag = true />
			<cfbreak />
		</cfif>
	</cfloop>
	<cfif not matchFlag>
		<cfset arrayAppend( notInDatabase, currentCaseNumber ) />
	</cfif>
</cfloop>

<cfdump var="#notInDatabase#" label="These are the case numbers not in the database.">
__________________
Ask if you have a question, but also help answer questions that you have knowledge of! Thanks, Brian.
How to Post a Question in the Forums

Reply With Quote
  #3  
Old January 3rd, 2005, 09:26 AM
Shmoo Shmoo is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Posts: 38 Shmoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 43 m 17 sec
Reputation Power: 4
Sorry, just getting back to this after the holidays

Thanks for the idea, but it didn't work...caused a few errors that I couldn't figure out. I should mention that we're running this on a CF 5 server (not MX), and using Sybase sql11 if that helps.

Any other ideas? I really wish #checkcase[i].RecordCount# would work, but either it simply doesn't, or I'm doing it wrong.

Reply With Quote
  #4  
Old January 3rd, 2005, 10:20 AM
kiteless kiteless is offline
Moderator
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Jun 2002
Location: Raleigh, NC
Posts: 3,627 kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Week 4 Days 10 h 8 m 55 sec
Reputation Power: 53
You're naming the queries like this "checkcase#i#", which would evaluate to a name like "checkcase3". But you're trying to reference it as an array, like #checkcase[i]#, but they are not arrays. You could be able to do something like:

<CFIF evaluate( 'checkcase#i#.RecordCount' ) IS "0">

Reply With Quote
  #5  
Old January 3rd, 2005, 01:20 PM
Shmoo Shmoo is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Posts: 38 Shmoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 43 m 17 sec
Reputation Power: 4
Well that certainly makes sense, thanks! I can't believe I forgot about evaluate, because one of the things I tried was #checkcase#i#.RecordCount# even though I knew that wouldn't work. Thanks again, kiteless

Reply With Quote
Reply

Viewing: Dev Shed ForumsProgramming Languages - MoreColdFusion Development > Is it possible to loop through multiple queries?


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway