|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
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
|
|||
|
|||
|
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>
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> ![]() Any help/advice would be appreciated, thanks. |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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"> |
|
#5
|
|||
|
|||
|
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
![]() |
![]() |
| Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > Is it possible to loop through multiple queries? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|