Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Exit

0

/t5/coldfusion-discussions/qoq-division-with-zero/td-p/8173878
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

Community guidelines

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

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

...
5
Replies
5

/t5/coldfusion-discussions/qoq-division-with-zero/m-p/8173879#M172103
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,

^_^

Community guidelines

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

jfb00
AUTHOR

Advisor
,

/t5/coldfusion-discussions/qoq-division-with-zero/m-p/8173880#M172104
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?

Community guidelines

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

/t5/coldfusion-discussions/qoq-division-with-zero/m-p/8173881#M172105
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,

^_^

Community guidelines

/t5/coldfusion-discussions/qoq-division-with-zero/m-p/8173882#M172106
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>

Community guidelines

jfb00
AUTHOR

Advisor
,

LATEST
/t5/coldfusion-discussions/qoq-division-with-zero/m-p/8173883#M172107
Mar 15, 2016
Mar 15, 2016

Copy link to clipboard

Copied

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

Best,

Community guidelines

Resources

Documentation

Copyright © 2023 Adobe. All rights reserved.