Help with hard query

From: The Magnet <art_at_unsu.com>
Date: Thu, 3 Mar 2011 14:46:28 -0800 (PST)
Message-ID: <464e5dac-8a36-432c-a8e7-d660d70984b6_at_v11g2000prb.googlegroups.com>



Hi,

I have this query (below), it is supposed to return 2 columns from different tables using subqueries. So, each query is a column. If data is found in both columns, data is returned. If one of the queries has no data, nothing is returned.

What I want is if one of the subqueries has no data because the WHERE fails, it should return NULL for that column value:

In the below query, if the query from revision_events_count_mv returns data and the query from guidance_events_count_mv returns none, the query should do just that, return data for column 1 and null for column 2.

Help anyone?

  SELECT SYSDATE event_date, revisions, guidance   FROM (SELECT revisions, guidance

        FROM ((SELECT SUM(revisions) OVER (PARTITION BY event_date) revisions

               FROM revision_events_count_mv
               WHERE event_date = TO_DATE('01312011','MMDDYYYY'))),
              (SELECT SUM(guidance) OVER (PARTITION BY event_date)
guidance
               FROM guidance_events_count_mv
               WHERE event_date = TO_DATE('04052009','MMDDYYYY')));
Received on Thu Mar 03 2011 - 16:46:28 CST

Original text of this message