Re: Query question

From: Luuk <Luuk_at_invalid.lan>
Date: Tue, 30 Aug 2011 20:35:59 +0200
Message-ID: <4e5d2d8f$0$2498$e4fe514c_at_news2.news.xs4all.nl>

On 30-08-2011 19:57, The Magnet wrote:
> 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;

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

Received on Tue Aug 30 2011 - 13:35:59 CDT