Re: DECODE
From: Neil.W.James <news_at_NONSPAM.familyjames.com>
Date: Sat, 17 Apr 2010 15:54:43 +0200
Message-ID: <4bc9bda2$0$2990$ba4acef3_at_reader.news.orange.fr>
On 17/04/2010 07:36, The Magnet wrote:
> On Apr 16, 5:33 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
>
> 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.
Date: Sat, 17 Apr 2010 15:54:43 +0200
Message-ID: <4bc9bda2$0$2990$ba4acef3_at_reader.news.orange.fr>
On 17/04/2010 07:36, The Magnet wrote:
> 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.
The nested case suggested earlier sounds good.
But looking at it another way, aren't you wanting to know whether the customer has a subproduct 238 or not. Sounds more like an existence test - particularly since you're not interested in how many matches there are.
Regards,
Neil
Received on Sat Apr 17 2010 - 08:54:43 CDT