| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Merge result of two queries?
Thanks Michel (and to all other who replied!)
Cheers,
Edward
"Michel Cadot" <micadot{at}altern{dot}org> wrote in message
news:43c201cc$0$7937$626a14ce_at_news.free.fr...
>
> "Edward" <egoduk_at_NOSPAM_hotmail.com> a écrit dans le message de news:
> 43c1ab7b$0$82642$ed2619ec_at_ptn-nntp-reader03.plus.net...
> | Hi Andy,
> |
> | Thanks for the tip.
> |
> | Your sql:
> | > select dateTable.date, nvl(table.count, 0)
> | > from dateTable
> | > left outer join table
> | > on (dt.date = t.date)
> |
> | became:
> | select dateTable3.dt, nvl(table1.cnt, 0)
> | from dateTable3
> | left outer join table1
> | on (dateTable3.dt = table1.dt);
> |
> | based on tables that actually look like this:
> |
> | table1 (col names: DT,SUM,CNT,WEEK_ID,BASE_DAY_NO)
> | 07/01/2006 2537 180 214 6
> | 06/01/2006 3575 229 214 5
> | 05/01/2006 1258 105 214 4
> | 04/01/2006 1206 111 214 3
> | 03/01/2006 2566 168 214 2
> | 31/12/2005 1000 100 213 6
> |
> | dateTable3 (col names: same as table1)
> | 07/01/2006 0 0 0 0
> | 06/01/2006 0 0 0 0
> | 05/01/2006 0 0 0 0
> | 04/01/2006 0 0 0 0
> | 03/01/2006 0 0 0 0
> | 02/01/2006 0 0 0 0
> | 01/01/2006 0 0 0 0
> | 31/12/2005 0 0 0 0
> |
> | Results:
> | 07/01/2006 180
> | 06/01/2006 229
> | 05/01/2006 105
> | 04/01/2006 111
> | 03/01/2006 168
> | 31/12/2005 100
> |
> | It's not finding:
> |
> | 02/01/2006 0
> | 01/01/2006 0
> |
> | Any idea what I'm doing wrong?
> |
> | (FYI dateTable3 and table1 are temporary tables created from more
> complex
> | queries)
> |
> | Thanks in advance for your assistance
> |
> | Edward
> |
> |
>
> SQL> select * from table1;
> DT SUM CNT WEEK_ID BASE_DAY_NO
> ---------- ---------- ---------- ---------- -----------
> 07/01/2006 2537 180 214 6
> 06/01/2006 3575 229 214 5
> 05/01/2006 1258 105 214 4
> 04/01/2006 1206 111 214 3
> 03/01/2006 2566 168 214 2
> 31/12/2005 1000 100 213 6
>
> 6 rows selected.
>
> SQL> select * from dateTable3;
> DT SUM CNT WEEK_ID BASE_DAY_NO
> ---------- ---------- ---------- ---------- -----------
> 07/01/2006 0 0 0 0
> 06/01/2006 0 0 0 0
> 05/01/2006 0 0 0 0
> 04/01/2006 0 0 0 0
> 03/01/2006 0 0 0 0
> 02/01/2006 0 0 0 0
> 01/01/2006 0 0 0 0
> 31/12/2005 0 0 0 0
>
> 8 rows selected.
>
> SQL> select t3.dt, greatest(nvl(t1.cnt,0),t3.cnt)
> 2 from dateTable3 t3, table1 t1
> 3 where t1.dt (+) = t3.dt
> 4 order by t3.dt desc
> 5 /
> DT GREATEST(NVL(T1.CNT,0),T3.CNT)
> ---------- ------------------------------
> 07/01/2006 180
> 06/01/2006 229
> 05/01/2006 105
> 04/01/2006 111
> 03/01/2006 168
> 02/01/2006 0
> 01/01/2006 0
> 31/12/2005 100
>
> 8 rows selected.
>
> Regards
> Michel Cadot
>
>
 |
 |