Re: Multiple selects in one
Date: Sat, 13 Mar 2010 03:47:08 -0800 (PST)
Message-ID: <aff0bd42-0b7e-4020-9520-940f1a979b0a_at_r1g2000yqj.googlegroups.com>
On Mar 12, 8:03 pm, The Magnet <a..._at_unsu.com> wrote:
> Just trying to get in one record whether or not they have any product
> <> 204 and if they have a product = 204. I figure the UNION will give
> me max 2 records, and then I can use LEAD to get the values from the
> second record into the first record.
This is something I forgot to mention in my initial post - your approach is potentially flawed in general if there is more than a single row with product_id != 204. You're incorrect that your UNION will give you only two rows then (it will give you as many as there are unique combinations of the values), and since your analytic functions don't define a deterministic order within a CUSTOMER_ID partition (the ORDER BY part should use something different than CUSTOMER_ID) your result is indeterministic with your current approach.
The PIVOT sample will simply pick the maximum value as defined in the aggregate function which not be what you want. You can simply try the different results by extending the sample data provided to more than two rows.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Sat Mar 13 2010 - 05:47:08 CST