Xref: alice comp.databases.oracle.server:57416
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!newsfeed.nyu.edu!cyclone.news.idirect.com!island.idirect.com!momma.bigmomma.com!not-for-mail
From: "Michael Zhang" <zhangliuqing@hotmail.com>
Newsgroups: comp.databases.oracle.server
References: <ubaj3.1898$be.58277@newscene.newscene.com>
Subject: Re: Problems with a View that denormalizes a table: any sugguestions?
Lines: 57
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-Mimeole: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <3ocj3.187$u3.213494272@momma.bigmomma.com>
Date: Wed, 14 Jul 1999 23:16:37 -0400
X-Complaints-To: abuse@bigmomma.com
X-Trace: momma.bigmomma.com 932008575 209.161.252.124 (Wed, 14 Jul 1999 23:16:15 EDT)
NNTP-Posting-Date: Wed, 14 Jul 1999 23:16:15 EDT
Organization: via Bigmomma - http://www.bigmomma.com/

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@mailexcite.com> wrote in message
news: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?