Re: Help with DECODE

From: Paul <mrpaulwass_at_hotmail.com>
Date: 18 Jan 2002 17:07:20 -0800
Message-ID: <bad8cee.0201181707.6b755458_at_posting.google.com>

Rob,

Forget the Decode nonsense.

You can do it with a decode if you really want. If you really do, then you can do it with one statement. You use least and greatest together.

decode(least(greatest(a,c), b), c, 'its in the middle', 'its not')

This decode will check to see if your date (value c) is between the start date A and end date B. (NOTE - Date check is inclusive of A and B)

But rather than do that, write your own function.

CREATE OR REPLACE FUNCTION check_dates(

   start_date IN     DATE,
   end_date   IN     DATE,
   my_date    IN     DATE )

RETURN NUMBER;
BEGIN    IF start_date > my_date THEN

     IF end_date < my_date THEN
      RETURN 1;   -- Value is in range!  Return whatever

    END IF;
  END IF;
  RETURN 0; -- Value not in range! Sorry

END check_dates;

Then in your SQL do something like

SELECT check_dates (To_Date('01052002 00:15:43','MMDDYYYY HH24:MI:SS')

                   ,To_Date('01022002 16:37:50','MMDDYYYY HH24:MI:SS')
                   ,SYSDATE - 10),
       SYSDATE "TODAY IS yada yada "

FROM ... It's much clearer to understand! And you can add in whatever logic you want in the function!

Good luck.

Paul

"rob" <rob_at_dsdelft.nl> wrote in message news:<a26glv$hli$1_at_news.tudelft.nl>...
> It should look something like this :
>
> select
> case when (sum_bgn_t < '01-05-2002 00:15:43' and sum_end_t > '01-02-2002
> 16:37:50' )
> then
> Ggpsmp03.Tot_Btl_Rjct_Q
> else
> 0
> end
> from ...
Received on Sat Jan 19 2002 - 02:07:20 CET