Re: Problems with a View that denormalizes a table: any sugguestions?

Hi:

You need to assign an alias to the to_date function, like to_char(date1, 'MMDDYYYY') date1, otherwise, there will be literal problem of the column name in the view.

Michael.

ramdan <ramdan_at_mailexcite.com> wrote in message news:ubaj3.1898$be.58277_at_newscene.newscene.com...
> I have a table, hourly, one row for each hour of day:
>
> acct# varchar2
> id varchar2
> date1 date
> hr number(14,4)
> reading number(14,4)
>
>
> 123
> texas
> 01/12/99
> 1
> 345
>
> i have a view(thanks to Tom from Oracle) that denormalizes it giving one
row
> with 24 columns one for each hour:
>
> create or replace denormal_view (id, readingdate, hr, reading1,
reading2...,
> reading24) select acct# || id, date1,
> sum(decode(hr,1,reading,0), reading1,
> sum(decode(hr,2,reading,0), reading2,
> sum(decode(hr,3,reading,0), reading3,
> .
> sum(decode(hr,24,reading,0), reading1
> from hourly
> group by acct#|| id , date
>
> this gives
>
> 123texas 01/12/99 12:00:00 1.000000000 345.0000000000
>
> Problem:
>
> The date as you can see shows up as DD/MM/YYYY HH, however i want it to be
> MMDDYYYY with no timestamp. I tried a to_char(date1, 'MMDDYYYY') but go
a
> message "literal does not agree with format" and it won't work. I did a
> trunc(date1) that didn't work. Any ideas to fix it?
>
> also I want the numbers in the view to show up as 12,2 not 14,4 if a
decimal
> otherwise decimal point is omitted any ideas?
Received on Wed Jul 14 1999 - 22:16:37 CDT