Skip to main content
J
jfb00
Inspiring
February 26, 2016
Answered

QoQ division with zero

  • February 26, 2016
  • 2 replies
  • 934 views

Hi All,

I have an issue with QoQ when the division is zero. I usually use nullif in oracle queries but it is not allow in QoQ.

How can I go around this issue?

Code example:

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

            select 'Item01' as DESCR

            <cfloop from="2011" to="2016" index="y">

                ,sum(FY_#y#)     as FY_#y#

                ,sum(COST_#y#) as COST_#y#

                ,sum(COST_#y#) / sum(FY_#y#) as PERC_#y#

            </cfloop>

            from qryItems

            group by DESCR

        </cfquery>

where for a year 2014 the sum(FY) = 0  and sum(COST)= 0

Thanks,

Johnny

    This topic has been closed for replies.
    Correct answer BKBK

    Sometimes playing dumb is the way to go. How about this:

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

        sum(FY_2011) as FY_2011, sum(FY_2012) as FY_2012, sum(FY_2013) as FY_2013, sum(FY_2014) as FY_2014, sum(FY_2015) as FY_2015, sum(FY_2016) as FY_2016

        from qryItems

    </cfquery>

    <cfif FYsum.FY_2011 NEQ 0 AND FYsum.FY_2012 NEQ 0 AND FYsum.FY_2013 NEQ 0 AND FYsum.FY_2014 NEQ 0 AND FYsum.FY_2015 NEQ 0 AND FYsum.FY_2016 NEQ 0>

    <!--- Note: It is efficient to use the FY sums already evaluated --->

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

        select 'Item01' as DESCR

        ,#FYsum.FY_2011# as FY_2011, #FYsum.FY_2012# as FY_2012, #FYsum.FY_2013# as FY_2013, #FYsum.FY_2014# as FY_2014, #FYsum.FY_2015# as FY_2015, #FYsum.FY_2016# as FY_2016

         ,sum(COST_2011) as COST_2011, sum(COST_2012) as COST_2012, sum(COST_2013) as COST_2013, sum(COST_2014) as COST_2014, sum(COST_2015) as COST_2015, sum(COST_2016) as COST_2016

        ,sum(COST_2011) / #FYsum.FY_2011# as PERC_2011, sum(COST_2012) / #FYsum.FY_2012# as PERC_2012, sum(COST_2013) / #FYsum.FY_2013# as PERC_2013, sum(COST_2014) / #FYsum.FY_2014# as PERC_2014, sum(COST_2015) / #FYsum.FY_2015# as PERC_2015, sum(COST_2016) / #FYsum.FY_2016# as PERC_2016

        from qryItems

        group by DESCR

    </cfquery>

    <cfelse><!--- At least one of the FY sums is 0 --->

    <!--- Do what you gotta do --->

    </cfif>

    2 replies

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    February 28, 2016

    Sometimes playing dumb is the way to go. How about this:

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

        sum(FY_2011) as FY_2011, sum(FY_2012) as FY_2012, sum(FY_2013) as FY_2013, sum(FY_2014) as FY_2014, sum(FY_2015) as FY_2015, sum(FY_2016) as FY_2016

        from qryItems

    </cfquery>

    <cfif FYsum.FY_2011 NEQ 0 AND FYsum.FY_2012 NEQ 0 AND FYsum.FY_2013 NEQ 0 AND FYsum.FY_2014 NEQ 0 AND FYsum.FY_2015 NEQ 0 AND FYsum.FY_2016 NEQ 0>

    <!--- Note: It is efficient to use the FY sums already evaluated --->

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

        select 'Item01' as DESCR

        ,#FYsum.FY_2011# as FY_2011, #FYsum.FY_2012# as FY_2012, #FYsum.FY_2013# as FY_2013, #FYsum.FY_2014# as FY_2014, #FYsum.FY_2015# as FY_2015, #FYsum.FY_2016# as FY_2016

         ,sum(COST_2011) as COST_2011, sum(COST_2012) as COST_2012, sum(COST_2013) as COST_2013, sum(COST_2014) as COST_2014, sum(COST_2015) as COST_2015, sum(COST_2016) as COST_2016

        ,sum(COST_2011) / #FYsum.FY_2011# as PERC_2011, sum(COST_2012) / #FYsum.FY_2012# as PERC_2012, sum(COST_2013) / #FYsum.FY_2013# as PERC_2013, sum(COST_2014) / #FYsum.FY_2014# as PERC_2014, sum(COST_2015) / #FYsum.FY_2015# as PERC_2015, sum(COST_2016) / #FYsum.FY_2016# as PERC_2016

        from qryItems

        group by DESCR

    </cfquery>

    <cfelse><!--- At least one of the FY sums is 0 --->

    <!--- Do what you gotta do --->

    </cfif>

    J
    jfb00Author
    Inspiring
    March 15, 2016

    Thanks for your reply and help. I did something similar.

    Best,

    WolfShade
    WolfShade
    Legend
    February 26, 2016

    How about a CASE/WHEN statement?

                <cfloop from="2011" to="2016" index="y">
    

                    ,sum(FY_#y#)    as FY_#y#
    

                    ,sum(COST_#y#) as COST_#y#
    

                  CASE WHEN sum(COST_#y#) = 0 THEN ,{something else}
    

                        WHEN sum(FY_#y#) = 0 THEN ,{another thing}
    

                        ELSE ,sum(COST_#y#) / sum(FY_#y#)
    

                  END   as PERC_#y#
    

                </cfloop>

    Or something similar?

    HTH,

    ^_^

    J
    jfb00Author
    Inspiring
    February 26, 2016

    Thanks for your reply and help.

    QoQ doesn't allow CASE also.

    http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html

    Anyone else?

    WolfShade
    WolfShade
    Legend
    February 26, 2016

    Does this NEED to be done via QoQ??  Is there no way to create a STORED PROCEDURE, for this?

    V/r,

    ^_^

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