Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

cfoutput group function

Posted on 2011-03-16
12
Medium Priority
?
356 Views
Last Modified: 2012-05-11
One check pays for multiple invoices.

I want this output to display one check number with one or more invoice numbers in the same line.

It works fine if 1 check paid 1 invoice, otherwise it lists the check each time it was used to pay an invoice. When I use the "group" function, it will list the check once, but will only list the first invoice it paid for.

Any ideas?
SELECT *
FROM invoice
LEFT OUTER JOIN Banking ON invoice.Check_Number=banking.Check_Number

<cfoutput query="vchecks" maxrows="500" group="check_number">

Open in new window

0
Comment
Question by:aka_FATCAT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 35153964
use the <cfoutput query="qchecks"> check @ main and inside use another question with cfoutput group attribute
0
 

Author Comment

by:aka_FATCAT
ID: 35153985
<cfoutput query="vchecks" maxrows="500">
<cfoutput group="check_number">#invoice_number#</cfoutput>
</cfoutput>

This returned every invoice number, not just the ones associated with that check.
0
 

Accepted Solution

by:
aka_FATCAT earned 0 total points
ID: 35154216
Got it.
<cfoutput query="vchecks" maxrows="500">
<cfloop query="invoicenumbers" >#invoice_number# - </cfloop>

Open in new window

0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 35154410
@ good u found answer!
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35156568
Assuming that your check number and your invoice numbers are coming from that one query, your answer is not optimal, all you need is the group by with a 2nd set of cfoutput tags within it.

Also, to ensure the grouping works, you have to order your query by check number so the check numbers are all grouped together

SELECT *
 FROM invoice
 LEFT OUTER JOIN Banking ON invoice.Check_Number=banking.Check_Number
order by check_number, invoice_date

<cfoutput query="vchecks" maxrows="500" group="check_number">
  #check_number# - <cfoutput>#invoice_number#</cfoutput>
</cfoutput>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35159407
I agree with gdemaria's answer. It's the more optimal approach.  

<cfoutput query="vchecks" maxrows="500"

... another performance tip, limit the number of rows in the db.  The current code actually pulls every record in the 2 tables, but only displays the first X records.  If you only need 500 records, specify that in your sql.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35159857
Got it.
<cfoutput query="vchecks" maxrows="500">
<cfloop query="invoicenumbers" >#invoice_number# - </cfloop>


Hm... on second glance, ... I don't believe that would work. At least not if there's more than 1 checknumber as your query suggests.
0
 

Author Comment

by:aka_FATCAT
ID: 35161380
Here is the complete working code.
<cfquery name="vchecks" datasource="#request.dataSource#">
SELECT *
FROM banking
WHERE 0=0
<!---LEFT OUTER JOIN Banking ON invoice.Check_Number=banking.Check_Number--->
<cfif isDefined ("URL.check_status")>
AND cstatid = '#URL.check_status#'
</cfif>
<cfif isDefined("FORM.check_date")>
AND check_date = '#FORM.check_date#'
<cfelseif isDefined("URL.check_date")>
AND check_date = '#URL.check_date#'
<cfelse>
AND check_date > '#DateFormat(startdate, "mm-dd-yyyy")#'
</cfif>
<cfif isDefined ("URL.date")>
ORDER BY check_date
<cfelse>
ORDER BY banking.check_number desc
</cfif>
</cfquery>


<cfoutput query="vchecks" maxrows="500">
<cfquery name="cstat" datasource="#request.dataSource#">
SELECT cstatid, check_status
FROM cstat
WHERE cstatid = '#vchecks.cstatid#'
</cfquery>
<cfquery name="invoicenumbers" datasource="#request.dataSource#">
SELECT invoice_number
FROM invoice
WHERE check_number = '#vchecks.check_number#'
</cfquery>

<tr>
    <td>#check_number#</td>
    <td>#DateFormat(check_date, "ddd, mm-dd-yyyy")#</td>
    <td>#pay_to#</td>
    <td><cfloop query="invoicenumbers" >#invoice_number# - </cfloop></td>
    <td>#DollarFormat(amount)#</td>
    <td>#notes#</td>
    <td>
    <cfform name="cstatus" method="post" action="/_admin/view_checks.cfm">
    <cfselect name="cstatus_select" query="cstatf" value="cstatid" selected="#cstat.cstatid#" display="check_status"/>
    <cfinput type="submit" name="submit" value="Submit">
    <cfinput type="hidden" value="#vchecks.cid#" name="cid">
    </cfform>
    
    </td>
</tr>
</cfoutput>
</table>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35161700

That is one way to do it, yes.  Did you post it so we could help you do it better or just to show the "answer?"

Right now you have one main query, plus two queries that run for EVERY check number, so if there are 50 checks, you will run 101 queries.   The way agx and I suggested, you would have one query no matter what.

You will also have 50 forms instead of one form.

If you want more help, let us know, if you were just posting because you wanted to show it working, that's fine too.
0
 

Author Comment

by:aka_FATCAT
ID: 35161793
Right now, I'm just happy it's working.

If you can tell me how to do it better as in more efficiently, I would be very grateful.

Thanks.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35161895
Something like this..
<cfquery name="vchecks" datasource="#request.dataSource#">
 SELECT TOP 500
        b.*
      , inv.invoice_number
      , cs.check_status
 FROM banking b
   left join invoice inv on inv.check_number = b.check_number
   left join cstat cs on cs.cstatId = b.cstatid
 WHERE 0=0
<!---LEFT OUTER JOIN Banking ON invoice.Check_Number=banking.Check_Number--->
 <cfif isDefined ("URL.check_status")>
  AND b.cstatid = '#URL.check_status#'
 </cfif>
 <cfif isDefined("FORM.check_date")>
  AND b.check_date = '#FORM.check_date#'
 <cfelseif isDefined("URL.check_date")>
  AND b.check_date = '#URL.check_date#'
 <cfelse>
  AND b.check_date > '#DateFormat(startdate, "mm-dd-yyyy")#'
 </cfif>
 <cfif isDefined ("URL.date")>
  ORDER BY b.check_date, b.check_number desc
 <cfelse>
  ORDER BY b.check_number desc
 </cfif>
</cfquery>

<cfform name="cstatus" method="post" action="/_admin/view_checks.cfm">


<cfoutput query="vchecks" group="check_number">
<tr>
    <td>#check_number#</td>
    <td>#DateFormat(check_date, "ddd, mm-dd-yyyy")#</td>
    <td>#pay_to#</td>
    <td><cfoutput>#invoice_number# - </cfoutput></td>
    <td>#DollarFormat(amount)#</td>
    <td>#notes#</td>
    <td>
      <cfselect name="cstatus_select#vchecks.currentRow#" query="cstatf" value="cstatid" selected="#vchecks.cstatid#" display="check_status"/>
      <cfinput  name="cid#vchecks.currentRow#" type="hidden" value="#vchecks.cid#" >
    </td>
</tr>
</cfoutput>
</table>

  <cfinput type="submit" name="submit" value="Submit">
</cfform>

Open in new window

0
 

Author Closing Comment

by:aka_FATCAT
ID: 35178889
Found the answer myself.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question