Re: Complex Query
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 16 Feb 2011 06:40:52 -0800 (PST)
Message-ID: <f8981a10-3227-432d-9f04-3e230baf8016_at_m7g2000vbq.googlegroups.com>
On Feb 15, 10:32 am, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> I am using this query. It returns information on existing orders the
> customer has, and ranks them based on the status.
>
> We have 25 products defined in a separate table. Rather than having
> to keep doing a COUNT to check for a given product, I am hoping to
> somehow modify this query such that it returns a record for ALL
> products. If the customer does not have that product, it will give
> NULL values for the status column. right now I cannot find any way to
> do that join, I know it is some outer join.
>
> It may be a WITH query, which I always have trouble creating, but
> maybe someone can help, I've been at this for a few days.
>
> So, for a customer with 4 orders, this query returns 4 records. I'd
> like it to return 25 (total orders in products table) and just a NULL
> or 'N' for products the customer does not have.
>
> SELECT email, customer_id, subproduct_id, description, status, rnum,
> status_rank
> FROM (SELECT email, customer_id, subproduct_id, status, rnum,
> status_rank, description
> FROM (SELECT email, c.customer_id, subproduct_id, status,
> description,
> CASE status
> WHEN 'Active' THEN 1
> WHEN 'Pending' THEN 2
> WHEN 'Complete' THEN 3
> WHEN 'Cancelled' THEN 4
> END status_rank,
> ROW_NUMBER() OVER (PARTITION BY subproduct_id
> ORDER BY subproduct_id) rnum
> FROM customer c, customer_account ca,
> customer_address cd, customer_order_vw co
> WHERE c.customer_id = ca.customer_id(+)
> AND c.customer_id = cd.customer_id(+)
> AND c.customer_id = co.customer_id(+)
> AND c.customer_id = 312039939)
> ORDER BY status_rank, rnum)
> WHERE rnum = 1;
Date: Wed, 16 Feb 2011 06:40:52 -0800 (PST)
Message-ID: <f8981a10-3227-432d-9f04-3e230baf8016_at_m7g2000vbq.googlegroups.com>
On Feb 15, 10:32 am, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> I am using this query. It returns information on existing orders the
> customer has, and ranks them based on the status.
>
> We have 25 products defined in a separate table. Rather than having
> to keep doing a COUNT to check for a given product, I am hoping to
> somehow modify this query such that it returns a record for ALL
> products. If the customer does not have that product, it will give
> NULL values for the status column. right now I cannot find any way to
> do that join, I know it is some outer join.
>
> It may be a WITH query, which I always have trouble creating, but
> maybe someone can help, I've been at this for a few days.
>
> So, for a customer with 4 orders, this query returns 4 records. I'd
> like it to return 25 (total orders in products table) and just a NULL
> or 'N' for products the customer does not have.
>
> SELECT email, customer_id, subproduct_id, description, status, rnum,
> status_rank
> FROM (SELECT email, customer_id, subproduct_id, status, rnum,
> status_rank, description
> FROM (SELECT email, c.customer_id, subproduct_id, status,
> description,
> CASE status
> WHEN 'Active' THEN 1
> WHEN 'Pending' THEN 2
> WHEN 'Complete' THEN 3
> WHEN 'Cancelled' THEN 4
> END status_rank,
> ROW_NUMBER() OVER (PARTITION BY subproduct_id
> ORDER BY subproduct_id) rnum
> FROM customer c, customer_account ca,
> customer_address cd, customer_order_vw co
> WHERE c.customer_id = ca.customer_id(+)
> AND c.customer_id = cd.customer_id(+)
> AND c.customer_id = co.customer_id(+)
> AND c.customer_id = 312039939)
> ORDER BY status_rank, rnum)
> WHERE rnum = 1;
How about some DDL and sample data to create these structures?
HTH -- Mark D Powell -- Received on Wed Feb 16 2011 - 08:40:52 CST