Skip navigation
jfb00
jfb00
Currently Being Moderated

query of query issue

Jul 18, 2012 5:30 AM

HI All,

I am using oracle 11 and CF 10.

How can I handle a division calculation in a query of query when the divider is zero?

 

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

    SELECT 25/0 AS value_1, 35/5 as value_2, 40/0 as value_3

    FROM dual

</cfquery>

 

I cannot use decode, NULLIF.

Thanks!

567 Views   4 Replies   Latest reply: Dan Bracuk, Jul 18, 2012 6:52 AM
 
Replies
  • Adam Cameron.
    Currently Being Moderated
    1. Adam Cameron.,
    Jul 18, 2012 5:49 AM   in reply to jfb00
    Report

    The short version is that you can't.

     

    I'd try to get the DB to do it instead, if poss: QoQ ain't that good at anything more than the basics.

     

    --

    Adam

     
    |
    Mark as:
  • Reed Powell
    Currently Being Moderated
    2. Reed Powell,
    Jul 18, 2012 6:02 AM   in reply to Adam Cameron.
    Report

    Like Adam said, try to do it in the db.  if you cannot, then instead of the QofQ just do a CFLOOP over the resultset - that will allow you to use any CF code you want to determine which values are null, and replace whatever resultset values with a new calculated value.

    -reed

     
    |
    Mark as:
  • jfb00
    Currently Being Moderated
    3. jfb00,
    Jul 18, 2012 6:11 AM   in reply to Reed Powell
    Report

    I cannot do it in the DB but this is a good idea, thanks!

    The issue is i am join two queries in one query.

    Best,

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    4. Dan Bracuk,
    Jul 18, 2012 6:52 AM   in reply to jfb00
    Report

    Why is joining two queries an issue?

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points