db2 CASE or CAST problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ballo
    #1

    db2 CASE or CAST problem

    Who have a explication or solution
    My Email info@info-tact.com


    Request #1 :
    SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
    10.00*1.00/nominal_total END as valeur FROM ( VALUES ( 0 ) ) as obs
    (nominal_total) ;

    The result is 9.0 But the request return and error -801 (division by
    zéro)

    Request #2 :
    SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
    10.00*1.00/dec(nominal_tot al) END as valeur FROM ( VALUES ( 0 ) )
    as obs (nominal_total) ;

    The result is good with not error

    I tried with double(nominal_ total) or int(nominal_tot al) and I received
    the same error then request #1

    Tags: None
    • Serge Rielau
      #2
      Re: db2 CASE or CAST problem

      ballo wrote:
      Who have a explication or solution
      My Email info@info-tact.com
      >
      >
      Request #1 :
      SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
      10.00*1.00/nominal_total END as valeur FROM ( VALUES ( 0 ) ) as obs
      (nominal_total) ;
      >
      The result is 9.0 But the request return and error -801 (division by
      zéro)
      >
      Request #2 :
      SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
      10.00*1.00/dec(nominal_tot al) END as valeur FROM ( VALUES ( 0 ) )
      as obs (nominal_total) ;
      >
      The result is good with not error
      >
      I tried with double(nominal_ total) or int(nominal_tot al) and I received
      the same error then request #1
      >
      Which version of DB2 are you on?
      There were issues in the past with DB2 being too aggressive with
      constant expressions evaluation. In the latest V8 fixpacks these should
      be fixed.

      Cheers
      Serge

      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

      IOD Conference
      http://www.ibm.com/software/data/ondemandbusiness/conf2006/

        Comment

        • ballo
          #3
          Re: db2 CASE or CAST problem

          Thank's for your help, My DB2 version is:

          Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and
          FixPak
          "10".

          Serge Rielau wrote:
          ballo wrote:
          Who have a explication or solution
          My Email info@info-tact.com


          Request #1 :
          SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
          10.00*1.00/nominal_total END as valeur FROM ( VALUES ( 0 ) ) as obs
          (nominal_total) ;

          The result is 9.0 But the request return and error -801 (division by
          zéro)

          Request #2 :
          SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
          10.00*1.00/dec(nominal_tot al) END as valeur FROM ( VALUES ( 0 ) )
          as obs (nominal_total) ;

          The result is good with not error

          I tried with double(nominal_ total) or int(nominal_tot al) and I received
          the same error then request #1
          Which version of DB2 are you on?
          There were issues in the past with DB2 being too aggressive with
          constant expressions evaluation. In the latest V8 fixpacks these should
          be fixed.
          >
          Cheers
          Serge
          >
          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab
          >
          IOD Conference
          http://www.ibm.com/software/data/ond...ness/conf2006/

            Comment

            • Serge Rielau
              #4
              Re: db2 CASE or CAST problem

              ballo wrote:
              Thank's for your help, My DB2 version is:
              >
              Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and
              FixPak
              "10".
              I believe this has been fixed after FP10. I'll inquire.
              What's happening here is that DB2's optimizer performs a few rules:
              1. Constant pull up
              nominal_total is replaced with 0
              2. Constant folding
              There is a (growing) list of functions which DB2 evaluates during
              compilation of the query.
              Obviously DEC() in FP 10 is not amongst them while double() and INT()
              and "/"() are.
              Now DB2 is supposed back of if any such compile time evaluation causes
              an error (such as division by 0) and wait for runtime execution. But
              it's not doing that for you.

              Cheers
              Serge
              --
              Serge Rielau
              DB2 Solutions Development
              IBM Toronto Lab

              IOD Conference
              http://www.ibm.com/software/data/ondemandbusiness/conf2006/

                Comment

                Previous template Next
                Working...