Skip to main content
M
MarianneValentine
Inspiring
October 29, 2008
Question

Subract sums of two queries from each other to get subtotal

  • October 29, 2008
  • 11 replies
  • 1958 views
Once again, I'm in over my head - help help!
I have been struggling with this off and on over the last couple weeks and I'm not sure to look for a solution.
Here's what I'm trying to do:

Schools send checks covering workshop fees, multiple registrations per workshop and per school. Subtotal the amounts of the checks per school. Add the amounts of the paid registration fees per school, and subtract the subtotal from the subtotal of checks. Display the result.

I feel like I'm going in the right direction, but I'm obviously missing something essential because I get this error:

The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.

Null Pointers are another name for undefined values.

The error occurred in C:\Inetpub\wwwroot\crtis\admin\schoolFin\allSchoolFin_credit.cfm: line 65

63 : </cfquery>
64 :
65 : <cfquery name="getSubTotal" dbtype="query">
66 : SELECT getSTChecks.s_SchoolName AS s_SchoolName, getSTWSCost.s_SchoolName, SUM(getSTChecks.STChecks - getSTWSCost.STWSCost) AS SubTotal
67 : FROM getSTChecks, getSTWSCost

I would be grateful for either a nudge toward some reading (CFWACK?) or if someone wants to take a look at my code and make a suggestion, that would be most welcome.
    This topic has been closed for replies.

    11 replies

    Show previous replies
    -
    -__cfSearching__-
    Inspiring
    October 29, 2008
    > Null Pointers are another name for undefined values.

    I have only skimmed the code, but noticed the initial query uses LEFT JOINs. So it is possible the values you are attempting to sum may be NULL. Sometimes QoQ have problems with queries that contain NULL values. Try replacing the null values with a default number like 0.

    The syntax may vary, but most databases support the COALESCE function. You can use it to return an alternate value if a column value is null.
    http://en.wikipedia.org/wiki/Null_(SQL)#COALESCE

    SELECT COALESCE(wrp.CheckAmount, 0) AS wrp_CheckAmount, ....etc...


    > <cfquery name="getSubTotal" dbtype="query">

    BTW, I think you are also missing a WHERE clause in the getSubTotal query


    That said, it may be possible to do the sums in your intial query. Assuming you are not using all of the extra values elsewhere in the page.




    M
    MarianneValentineAuthor
    Inspiring
    October 29, 2008
    Thanks so much for taking a look!

    Re the nulls - the only place where I could think that null values were being accessed was the WorkshopRegPaymentID in the WorkshopRegistration table, so I just went ahead and changed those to '0'. No luck.

    Then I thought, hmmm, maybe I don't need that value after all, since all I'm trying to do at this point is grab the checks for each school and the paid registrations for each school. (Later I'll need to connect the payment id's, since I want to build something that will apply payments for each registration and attribute them to certain checks.) I commented those lines out (just in case), and made the other changes you suggested.

    So here's where I'm at now - I seem to have moved forward somewhat, but now it's telling me that "wr.WorkshopSessionID" could not be bound. Very puzzling.
    -
    -__cfSearching__-
    Inspiring
    October 29, 2008

    > Re the nulls - the only place where I could think that null values were being
    > accessed was the WorkshopRegPaymentID in the WorkshopRegistration table, so I
    > just went ahead and changed those to '0'. No luck.

    Well, there may not necessarily be literal nulls in your tables. They can occur as a result of the LEFT JOINs. With OUTER joins, if there are no matching records in the outer table(s), a NULL will be returned instead. That is why you must use COALESCE to replace the nulls with another value.
    http://en.wikipedia.org/wiki/JOIN#Outer_joins


    > but now it's telling me that "wr.WorkshopSessionID" could not be bound. Very puzzling.
    > FROM WorkshopRegistration as wr, WorkshopRegPayment as wrp

    Remove the extra reference to ", WorkshopRegPayment as wrp". But you will still get an error because some of its columns are still referenced in the SELECT list.

    So do you actually need all of the columns in your SELECT list? (Still trying to get a handle on all the relationships.) If not, there may be a simpler query that would get you the totals needed without all of the QoQ's...

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices