• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

QoQ division with zero

Advisor ,
Feb 26, 2016 Feb 26, 2016

Copy link to clipboard

Copied

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

Views

595

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , Feb 28, 2016 Feb 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

...

Votes

Translate

Translate
LEGEND ,
Feb 26, 2016 Feb 26, 2016

Copy link to clipboard

Copied

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,

^_^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Feb 26, 2016 Feb 26, 2016

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 26, 2016 Feb 26, 2016

Copy link to clipboard

Copied

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

V/r,

^_^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 28, 2016 Feb 28, 2016

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Mar 15, 2016 Mar 15, 2016

Copy link to clipboard

Copied

LATEST

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

Best,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation