Re: Help with hard query

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 3 Mar 2011 23:57:25 -0800 (PST)
Message-ID: <9d78b62a-4523-4ac2-9d23-efc5d22c314e_at_u14g2000vbg.googlegroups.com>

On 3 Mrz., 23:46, The Magnet <a..._at_unsu.com> wrote:
> 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')));

select
 sysdate event_date,
(select sum(revisions)
 from revision_events_count_mv
 where event_date=date '2009-04-05') revisions, (select sum(guidance)
 from guidance_events_count_mv
 where event_date=date '2011-01-31') guidance from dual
/

Best regards

Maxim Received on Fri Mar 04 2011 - 01:57:25 CST