Re: DECODE

From: The Magnet <art_at_unsu.com>
Date: Fri, 16 Apr 2010 22:36:55 -0700 (PDT)
Message-ID: <cf4e3a23-e8a0-48f2-a307-e98d4b69a5a5_at_x12g2000yqx.googlegroups.com>



On Apr 16, 5:33 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> On Apr 16, 6:03 pm, The Magnet <a..._at_unsu.com> wrote:
>
> snip
>
> > Can you nest DECODE with different field criteria?  Here is our
> > DECODE:
>
> > DECODE(SUM(DECODE(subproduct_id, 238, 1, 0)) OVER (PARTITION BY
> > customer_id),0,'N','Y') ultimate_trader
>
> > Now, with that, if the result is Y, meaning that they DO have this 238
> > product, I want to see if they have a status = 'Active'.  So, is there
> > a way to implement yet another level, testing the status = 'Active' if
> > the result of the above statement is 'Y'?
>
> Did you try testing what you wanted to do?
>
> Have you ever heard of inline views?

Oh yeah, the hard part about this whole thing is that I need to PARTITION it by customer ID. So, within each partition I need to know if the customer has product 238 and if he does if the status is Active.

All in one select. How fun. Received on Sat Apr 17 2010 - 00:36:55 CDT

Original text of this message