Re: Merge result of two queries?

"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 Received on Mon Jan 09 2006 - 00:25:17 CST