Hi,

We have a massive query. But there is a problem. One of the
subqueries does not return any rows, therefore the entire query
returns no rows. I need to make it is one of the subqueries has no
results, then it just gets NULL or 0. The query is below. Can
someone help?

Thanks!


SELECT group_name, sales, cancelled, net_sales, provider_fee,
gross_profit, authorize_fee, authorize_fee_cancel, credit_card_fee,
partner_fee, net_income
FROM (
SELECT group_name, sales, cancelled, (sales - cancelled) AS net_sales,
0 provider_fee,
ROUND((sales - cancelled),2) AS gross_profit, authorize_fee,
authorize_fee_cancel,
0 AS credit_card_fee, partner_fee,
ROUND((sales - cancelled - authorize_fee - authorize_fee_cancel
- partner_fee),2) AS net_income,
ROW_NUMBER() OVER (PARTITION BY newsletter_id ORDER BY
newsletter_id) rnum
FROM (SELECT group_name, order_quantity * price AS sales,
pg.newsletter_id
FROM zacks.customer_order_vw co, zacks.product_groups_mv pg
WHERE co.type = 2
AND co.subproduct_id = pg.newsletter_id
AND co.transaction_id <> -1
AND co.transaction_id IS NOT NULL
AND co.date_entered BETWEEN TO_DATE('08282011','MMDDYYYY')
AND TO_DATE('08292011','MMDDYYYY')),
(SELECT NVL(ca.amount,0) AS cancelled
FROM zacks.customer_order_vw co, zacks.product_groups_mv pg,
zacks.cancellation_mv ca
WHERE co.type = 2
AND co.subproduct_id = pg.newsletter_id
AND co.order_item_id = ca.order_item_id
AND co.transaction_id <> -1
AND co.transaction_id IS NOT NULL
AND ca.cancel_date BETWEEN TO_DATE('08282011','MMDDYYYY') AND
TO_DATE('08292011','MMDDYYYY')),
(SELECT SUM(.01) AS authorize_fee
FROM zacks.customer_order_vw co, zacks.product_groups_mv pg
WHERE co.type = 2
AND co.subproduct_id = pg.newsletter_id
AND co.transaction_id <> -1
AND co.transaction_id IS NOT NULL
AND co.date_entered BETWEEN TO_DATE('08282011','MMDDYYYY')
AND TO_DATE('08292011','MMDDYYYY')),
(SELECT NVL(SUM(.01),0) AS authorize_fee_cancel
FROM zacks.customer_order_vw co, zacks.cancellation_mv ca,
zacks.product_groups_mv pg
WHERE co.type = 2
AND co.subproduct_id = pg.newsletter_id
AND co.order_item_id = ca.order_item_id
AND co.transaction_id <> -1
AND co.transaction_id IS NOT NULL
AND ca.cancel_date BETWEEN TO_DATE('08282011','MMDDYYYY') AND
TO_DATE('08292011','MMDDYYYY')
AND co.order_item_id IN (
SELECT c.order_item_id
FROM zacks.customer_order_vw c
WHERE c.date_entered BETWEEN
TO_DATE('08282011','MMDDYYYY') AND TO_DATE('08292011','MMDDYYYY'))),
(SELECT cr.rate * co.price / 100 AS partner_fee
FROM zacks.customer_order_vw co, zacks.product_groups_mv pg,
zacks.commission_rate_mv cr, zacks.partner_mv pt
WHERE co.type = 2
AND co.subproduct_id = pg.newsletter_id
AND co.transaction_id <> -1
AND co.transaction_id IS NOT NULL
AND co.reference = pt.reference
AND pt.reference = cr.reference
AND co.date_entered BETWEEN TO_DATE('08282011','MMDDYYYY')
AND TO_DATE('08292011','MMDDYYYY')
UNION
SELECT -SUM(ca.amount * cr.rate / 100) AS partner_fee
FROM zacks.customer_order_vw co, zacks.cancellation_mv ca,
zacks.product_groups_mv pg, zacks.commission_rate_mv cr,
zacks.partner_mv pt
WHERE co.type = 2
AND co.subproduct_id = pg.newsletter_id
AND co.transaction_id <> -1
AND co.transaction_id IS NOT NULL
AND co.reference = pt.reference
AND pt.reference = cr.reference
AND ca.cancel_date BETWEEN TO_DATE('08282011','MMDDYYYY') AND
TO_DATE('08292011','MMDDYYYY')))
WHERE rnum = 1;

On 30-08-2011 19:57, The Magnet wrote:
i would hit the 'DELETE'key, and rewrite the complete query
because, if you have a properly designed database,
i REALLY thing this (whatever you are trying to do here) could be done
better/simpler.....

--
Luuk

The Magnet wrote:
That is normally solved with an "outer join". Check it out.

On Aug 30, 1:56*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
I'm not sure how I would do this with an outer join, since all of the
columns are basically subqueries. If one of the subqueries returns no
results, why does the entire query fail?

On Aug 30, 1:03*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:

It fails because it's an inner join:

SQL> select w.val, x.val, y.val, z.val
2 from
3 (select id, val from a where id =13) w,
4 (select id, val from b where id =13) x,
5 (select id, val from c where id =13) y,
6 (select id, val from d where id =13) z
7 where x.id = y.id and y.id = z.id and z.id = w.id;

no rows selected

SQL>
SQL> select w.val, x.val, y.val, z.val
2 from
3 (select id, val from a where id =13) w right outer join (select
id, val from b where id =13) x on (w.id = x.id)
4 right outer join (select id, val from c where id =13) y on (x.id
= y.id)
5 right outer join (select id, val from d where id =13) z on (z.id
= y.id);

VAL VAL VAL VAL
-------- -------- -------- --------
00000013 00000013 00000013

SQL>

Make it an outer join of some sort and it should return results.


David Fitzjarrell

The Magnet wrote:
As David showed you, you are still joining your "subqueries" (I think in your case they are
views). The minute one of them returns no rows, the whole query returns no rows.

Anyhow, you better take Luuk's advice and have someone who knows SQL & RDBMS rewrite your
selection. Or was this query generated?

On Aug 30, 4:30*pm, ddf <orat... (AT) msn (DOT) com> wrote:

Thanks Dave.

I got so frustrated that I broke the query into individual queries,
stored the results in an object table and queried from that table. A
hell of a lot more work. But, I'll try your solution too.