Re: SQL Question

Thank you for the prompt replies.

Using the single select with the large decode statement works corretly.

However, with the small data set I have right now I am concerned about the speed issues.

Any recommendations for methods to improve the speed.

Examples (comments appreciated)

1. Rules on the data. (possibly eliminate the need for some functions like TRUNC)
2. Caching the results in another table that is updated by a trigger on the main table.
3. Snapshot view (any issues with this?)

Thank you

Craig

Michel Cadot <micadot_at_netcourrier.com> wrote in message news:8641a6$897$1_at_news3.isdnet.net...
> May be something like that:
>
> v734> create table t (start_date date,
> 2 month1_data number, month2_data number,
> 3 month3_data number, month4_data number,
> 4 month5_data number, month6_data number);
>
> Table created.
>
> v734> insert into t values (to_date('01/01/2000','DD/MM/YYYY'),
> 2 1000,1500,1200,1900,1400,900);
> v734> insert into t values (to_date('01/02/2000','DD/MM/YYYY'),
> 2 200,400,300,600,300,100);
> v734> insert into t values (to_date('01/03/2000','DD/MM/YYYY'),
> 2 1100,1700,1600,1200,1000,700);
> v734> commit;
>
> Commit complete.
>
> v734> select '01-JAN-2000' "Start date",
> 2 decode(trunc(months_between(start_date,
> 3
to_date('01/01/2000','DD/MM/YYYY'))),
> 4 0, month1_data, 0) "Month 1",
> 5 decode(trunc(months_between(start_date,
> 6
to_date('01/01/2000','DD/MM/YYYY'))),
> 7 0, month2_data, 1, month1_data, 0) "Month 2",
> 8 decode(trunc(months_between(start_date,
> 9
to_date('01/01/2000','DD/MM/YYYY'))),
> 10 0, month3_data, 1, month2_data, 2, month1_data, 0)
"Month 3",
> 11 decode(trunc(months_between(start_date,
> 12

to_date('01/01/2000','DD/MM/YYYY'))),
> 13 0, month4_data, 1, month3_data, 2, month2_data, 3,
> month1_data,
> 14 0) "Month 4",
> 15 decode(trunc(months_between(start_date,
> 16
to_date('01/01/2000','DD/MM/YYYY'))),
> 17 0, month5_data, 1, month4_data, 2, month3_data, 3,
> month2_data,
> 18 4, month1_data, 0) "Month 5",
> 19 decode(trunc(months_between(start_date,
> 20
to_date('01/01/2000','DD/MM/YYYY'))),
> 21 0, month6_data, 1, month5_data, 2, month4_data, 3,
> month3_data,
> 22 4, month2_data, 5, month1_data, 0) "Month 6"
> 23 from t
> 24 where start_date >= to_date('01/01/2000','DD/MM/YYYY')
> 25 order by start_date
> 26 /
>
> Start date Month 1 Month 2 Month 3 Month 4 Month 5
Month 6
> ----------- ---------- ---------- ---------- ---------- ---------- -------

---


> 01-JAN-2000       1000       1500       1200       1900       1400


900


> 01-JAN-2000          0        200        400        300        600


300


> 01-JAN-2000          0          0       1100       1700       1600


1200


>

> 3 rows selected.


>

>

> --

> Have a nice day

> Michel

>

>

> Craig Grell <craigg_at_thewebmarket.com> a écrit dans le message :

> 3884d930_2_at_news.cadvision.com...

> > I have a table with the following structure.

> >

> > start date, month1_data, month2_data, month3_data, month4_data,

month5_data,


> > month6_data

> >

> > with the following information

> >

> > '01-JAN-2000', 1000, 1500, 1200, 1900, 1400, 900


> > '01-FEB-2000', 200, 400, 300, 600, 300, 100

> > '01-MAR-2000', 1100, 1700, 1600, 1200, 1000, 700

> >

> >

> > How can I select this information to make the monthly data align so that

the


> > result of the select would look like the following when aligned on the


date


> > '01-JAN-2000'

> >

> > '01-JAN-2000', 1000, 1500, 1200, 1900, 1400, 900


> > '01-JAN-2000', 0, 200, 400, 300, 600, 300

> > '01-JAN-2000', 0, 0, 1100, 1700, 1600, 1200

> >

> >

> >

> > Thank you for any suggestions

> >

> > Craig

> >

> >

>

>