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

From: ramdan <ramdan_at_mailexcite.com>
Date: 14 Jul 1999 19:47:04 -0500
Message-ID: <ubaj3.1898$be.58277@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 - 19:47:04 CDT