pull information from loop query (for lack of a better way to explain)

I am trying to pull out information from a loop so that i can then run a query on it outside the loop, for testing, i just have had it show the information that is being placed into the testvar variable.  the tempvar variable pulls information just fine, but testvar always comes out blank.  

I have this:

 <cfoutput>
<cfset officeIndex = listFindNoCase(showresults.columnlist, "office")>
<cfset list = listAppend(listGetAt(showresults.columnlist, officeIndex), listDeleteAt(showresults.columnlist, officeIndex))>
<table border="1">
  <tr>
    <cfloop list="#list#" index="column">
      <th>#column#</th>
    </cfloop><th>Total Errors Found</th><th>testvar</th>
  </tr>
<cfloop from="1" to="#showresults.recordcount#" index="i"><cfset tempvar = 0><cfset testvar = 'x'>
  <tr>
    <cfloop list="#list#" index="column">
      <td><cfif IsNumeric(showresults[column][i]) IS "No">#showresults[column][i]#<cfset testvar = '#showresults[column][i]#'>
        <cfelse>#DollarFormat(showresults[column][i])#<cfset tempvar = tempvar + #showresults[column][i]#></cfif></td>
    </cfloop><td>#DollarFormat(tempvar)#</td><td>#testvar#</td>
  </tr>
</cfloop>
</table>

</cfoutput>

RussoMAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mosphatCommented:
If testvar always comes out blank that is due to the last non-numeric column being empty. Are you sure that is what is supposed to happen? If the query consists of multiple non-numeric columns, testvar is set again and again, only displaying the last column.

What exactly are you trying to achieve?
0
mrichmonCommented:
Try this:
<cfset officeIndex = listFindNoCase(showresults.columnlist, "office")>
<cfset list = listAppend(listGetAt(showresults.columnlist, officeIndex), listDeleteAt(showresults.columnlist, officeIndex))>
<table border="1">
  <tr>
    <cfloop list="#list#" index="column">
      <th><cfoutput>#column#</cfoutput></th>
    </cfloop><th>Total Errors Found</th><th>testvar</th>
  </tr>

<cfoutput query="showresults">
<cfset tempvar = 0>
<cfset testvar = 'x'>
  <tr>
    <cfloop list="#list#" index="column">
      <td><cfif IsNumeric(showresults[column]) IS "No">#showresults[column]#<cfset testvar = '#showresults[column]#'>
       <cfelse>#DollarFormat(showresults[column])#<cfset tempvar = tempvar + #showresults[column]#></cfif></td>
    </cfloop><td>#DollarFormat(tempvar)#</td><td>#testvar#</td>
  </tr>
</cfloop>
</cfoutput>
</table>
0
RussoMAAuthor Commented:
mrichmon:  i get an internal server error 500 with your code...

mosphat:  i am trying to pull out the office name so that i can run it against another query to pull another field of values from another table.

specifically, i want to be able to run this:

<cfquery name="getoffice" datasource="chcap">
SELECT office, draft
FROM odraft
WHERE office LIKE '#testvar#'
</cfquery>
#DollarFormat(getoffice.draft)#

and have it be the rightmost column of the data output.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

mosphatCommented:
Hmm, I might be wrong but I have this feeling that what you're trying to do can be done in the first query as well. What does the sql look like that is behind showresults?
0
RussoMAAuthor Commented:
well, i can change the crosstab query to include the draft amount, but it places it within the same data pool as all the others, and it adds it to the total, whereas i need it to be separate.  

i like running the sum as the right column, then adding on the draft amount to the right of that sum column... if possible.

<cfquery name="showresults" datasource="chcap">
SELECT *
FROM qryReport
ORDER BY office
</cfquery>

basic.
0
RussoMAAuthor Commented:
or are you saying that the loop can be extended for another column to pull the data?  and that i could sum the data in another query and pull it into the loop as well?
0
mosphatCommented:
RussoMa, the testvar column equals the last non-numeric column, doesn't it? What exactly is that column for?

BTW, <cfif IsNumeric(showresults[column][i]) IS "No"> has a faster equivalent: <cfif not IsNumeric(showresults[column][i])>


mrichmon, showresults[column] always returns the first queryrow. showresults[column][currentrow] would be the correct way, but it would do the exact same as the code RussoMA already has.
0
RussoMAAuthor Commented:
there is only one non-numeric column, the office column.  everything else has a currency.
0
mosphatCommented:
Then testvar should contain the office name, right? If not, could you try to explain again what your code is supposed to do?
0
RussoMAAuthor Commented:
yes, it should pull out the office name so that it can then be used as the criteria for the next query to be output on the same line.
0
mosphatCommented:
Well, you know the column name is office, so you can access it as directly:
I put in the query as well, but I'll guess you know what you need it for better than I do :)
Still, I think this can be solved with a single query. But since it's late overhere (and I can't think that straight anymore) I'll look into it again tomorrow.

<cfoutput>
<cfset officeIndex = listFindNoCase(showresults.columnlist, "office")>
<cfset list = listAppend(listGetAt(showresults.columnlist, officeIndex), listDeleteAt(showresults.columnlist, officeIndex))>
<table border="1">
  <tr>
    <cfloop list="#list#" index="column">
      <th>#column#</th>
    </cfloop><th>Total Errors Found</th><th>testvar</th>
  </tr>
<cfloop from="1" to="#showresults.recordcount#" index="i"><cfset tempvar = 0><cfset testvar = 'x'>
  <tr>
    <cfloop list="#list#" index="column">
      <td>
      <cfif not IsNumeric(showresults[column][i])>
        #showresults[column][i]#
       <cfelse>
        #DollarFormat(showresults[column][i])#
        <cfset tempvar = tempvar + #showresults[column][i]#>
       </cfif>
        </td>
    </cfloop>
    <td>#DollarFormat(tempvar)#</td>
    <td>#showresults.office[i]#</td>
  </tr>
<cfquery name="getoffice" datasource="chcap">
SELECT office, draft
FROM odraft
WHERE office LIKE '#showresults.office[i]#'
</cfquery>
... do something with the query results here...
</cfloop>
</table>

</cfoutput>
0
RussoMAAuthor Commented:
will have to give it a try monday.  looks to have the logic i would expect it to work.
0
mosphatCommented:
Can't you use this kind of construct to get the draft column?

<cfquery name="showresults" datasource="chcap">
SELECT q.*, o.draft
FROM qryReport q
JOIN odraft o
WHERE o.office = q.office
</cfquery>

The output would be something like this:

<cfoutput>
<cfset officeIndex = listFindNoCase(showresults.columnlist, "office")>
<cfset draftIndex = listFindNoCase(showresults.columnlist, "draft")>
<cfset list = listDeleteAt(listDeleteAt(showresults.columnlist, officeIndex), draftIndex)>
<table border="1">
  <tr>
    <th>Office</th>
    <cfloop list="#list#" index="column">
      <th>#column#</th>
    </cfloop><th>Total Errors Found</th>
    <th>draft</th>
  </tr>
<cfloop from="1" to="#showresults.recordcount#" index="i">
    <cfset tempvar = 0>
    <cfset testvar = 'x'>
  <tr>
    <td>#showresults.office[i]#</td>
    <cfloop list="#list#" index="column">
      <td>#DollarFormat(showresults[column][i])#<td>
      <cfset tempvar = tempvar + showresults[column][i]>
    </cfloop>
    <td>#DollarFormat(tempvar)#</td>
    <td>#showresults.draft[i]#</td>
  </tr>
</cfloop>
</table>

</cfoutput>


Of course this only works if there's one draft for each office. If that's not the case, just say so. There are solutions for that too.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RussoMAAuthor Commented:
Brings about an ODBC error saying there is a syntax error in FROM clause.  Here's all the code:

<cfquery name="showresults" datasource="chcap">
SELECT qryReport.*, odraft.draft
FROM qryReport
JOIN odraft
WHERE odraft.office = qryReport.office
</cfquery>

<body>

<cfoutput>
<cfset officeIndex = listFindNoCase(showresults.columnlist, "office")>
<cfset draftIndex = listFindNoCase(showresults.columnlist, "draft")>
<cfset list = listDeleteAt(listDeleteAt(showresults.columnlist, officeIndex), draftIndex)>
<table border="1">
  <tr>
    <th>Office</th>
    <cfloop list="#list#" index="column">
      <th>#column#</th>
    </cfloop><th>Total Errors Found</th>
    <th>draft</th>
  </tr>
<cfloop from="1" to="#showresults.recordcount#" index="i">
    <cfset tempvar = 0>
    <cfset testvar = 'x'>
  <tr>
    <td>#showresults.office[i]#</td>
    <cfloop list="#list#" index="column">
      <td>#DollarFormat(showresults[column][i])#<td>
      <cfset tempvar = tempvar + showresults[column][i]>
    </cfloop>
    <td>#DollarFormat(tempvar)#</td>
    <td>#showresults.draft[i]#</td>
  </tr>
</cfloop>
</table>

</cfoutput>
0
mosphatCommented:
Sorry... the WHERE keyword should be the ON keyword. :)
0
RussoMAAuthor Commented:
still get the syntax error on the FROM part..

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.  
 
0
mosphatCommented:
I don't know why, but somewhere along the road I got the assumption you were using MS SQL Server.
MS Access likes to have the query this way:

SELECT qryReport.*, odraft.draft
FROM qryReport, odraft
WHERE odraft.office = qryReport.office
0
RussoMAAuthor Commented:
thanks, all is well now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.