[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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>

0
RussoMA
Asked:
RussoMA
  • 9
  • 8
1 Solution
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now