Results 1 to 3 of 3

Thread: decode

  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: decode

    I am trying to perform the following logic:

    ont.reminder_nbr: number of reminders that are set up to send
    po.organizer_rmndr_sent_nbr: number of reminders that have been sent

    The number of the first value minus the second value is the number of remaining reminders. However, if the final number is negative then I want to display 0. Is this possible using a decode similar to the one below? If not is there another Oracle function that can be used?

    decode(ont.reminder_nbr - nvl(po.organizer_rmndr_sent_nbr) < 0, 0, ont.reminder_nbr - nvl(po.organizer_rmndr_sent_nbr))

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You can try using CASE (if you are running 8i and above):

    Code:
    SELECT 
     CASE
      WHEN (ont.reminder_nbr-nvl(po.organizer_rmndr_sent_nbr,0)) < 0 THEN 0 
       ELSE  (ont.reminder_nbr-nvl(po.organizer_rmndr_sent_nbr,0))
     END CASE
      FROM yourtable
    If you are not using Oracle 8i and above, you can try then:

    Code:
    SELECT
     decode(((ont.reminder_nbr-po.organizer_rmndr_sent_nbr)+abs(ont.reminder_nbr-po.organizer_rmndr_sent_nbr)),0,0,ont.reminder_nbr-po.organizer_rmndr_sent_nbr)
      FROM your table
    Im guessing ont.reminder_nbr is your FIRST value AND po.organizer_rmndr_sent_nbr your second.
    Last edited by JMartinez; 07-28-04 at 20:34.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select greatest( (ont.reminder_nbr-po.organizer_rmndr_sent_nbr),0)
    from my_table ont
    where ...
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •