Query question

From: The Magnet <art_at_unsu.com>
Date: Tue, 30 Aug 2011 10:57:31 -0700 (PDT)
Message-ID: <18836bcb-a782-41e5-bb9e-210cf8148ee9_at_y8g2000prd.googlegroups.com>

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; Received on Tue Aug 30 2011 - 12:57:31 CDT