DECODE
in [Oracle]
|
From: The Magnet on 16 Apr 2010 18:03 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'?
From: John Hurley on 16 Apr 2010 18:33 On Apr 16, 6:03 pm, The Magnet <a...(a)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?
From: The Magnet on 17 Apr 2010 01:33 On Apr 16, 5:33 pm, John Hurley <hurleyjo...(a)yahoo.com> wrote: > On Apr 16, 6:03 pm, The Magnet <a...(a)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? Been trying things, basically it is something like this: IF product = 238 THEN IF status = 'Active' THEN x = 'Y' ELSE x = 'N' END IF END IF I am looking to put that kind of logic into a SELECT statement.
From: The Magnet on 17 Apr 2010 01:36 On Apr 16, 5:33 pm, John Hurley <hurleyjo...(a)yahoo.com> wrote: > On Apr 16, 6:03 pm, The Magnet <a...(a)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.
From: Robert Klemme on 17 Apr 2010 03:51 On 17.04.2010 07:33, The Magnet wrote: > On Apr 16, 5:33 pm, John Hurley<hurleyjo...(a)yahoo.com> wrote: >> On Apr 16, 6:03 pm, The Magnet<a...(a)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? > > > Been trying things, basically it is something like this: > > IF product = 238 THEN > IF status = 'Active' THEN > x = 'Y' > ELSE > x = 'N' > END IF > END IF > > > I am looking to put that kind of logic into a SELECT statement. Did you try nested CASE? robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
|
Next
|
Last
Pages: 1 2 Prev: 11gR2 Or 10gR2 on Linux x64? Next: What does the COST(%CPU) in explain plan mean? |