Help with DECODE

From: John Kramer <john_at_ekramer.net>
Date: 16 Jan 2002 17:15:52 -0800
Message-ID: <fbcef4df.0201161715.4f213b6f_at_posting.google.com>


I'm having a problem with the DECODE function that I can not figure out. The error I'm getting is inconsistent datatypes. A simplified version of what I'm using follows:

SELECT
    Decode(Least(sum_bgn_t, To_Date('01-05-2002 00:15:43', 'MM-DD-YYYY HH24:MI:SS')), sum_bgn_t,

        Decode(Greatest(sum_bgn_t, To_Date('01-05-2002 01:15:43',
'MM-DD-YYYY HH24:MI:SS')), sum_bgn_t,

            0,
        Decode(Greatest(sum_end_t, To_Date('01-02-2002 16:37:50',

'MM-DD-YYYY HH24:MI:SS')), sum_end_t,
Decode(Least(sum_end_t, To_Date('01-02-2002 16:37:50',
'MM-DD-YYYY HH24:MI:SS')), sum_end_t,
0, Ggpsmp03.Tot_Btl_Rjct_Q), 0), 0),

    0),
FROM ... What I'm trying to do is this:
If sum_bgn_t < '01-05-2002 00:15:43' and sum_end_t > '01-02-2002 16:37:50' Then

     Return Ggpsmp03.Tot_Btl_Rjct_Q
Else

     Return 0
End If

Unfortunately Oracle requires me to do a very convoluted/nested DECODE with GREATEST and LEAST functions embedded. Any help on a more simple way to do this is appreciated. Or, a solution as to how to make this work at all will be a lifesaver. Thanks in advance. Received on Thu Jan 17 2002 - 02:15:52 CET

Original text of this message