QoQ division with zero

[jfb00]

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

[WolfShade]

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,

^_^

[jfb00]

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]

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

V/r,

^_^

[BKBK · Accepted solution]

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>

[jfb00]

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

Best,