Re: Create View

On Aug 16, 11:09 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Aug 16, 9:41 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Aug 16, 8:48 am, learning_co..._at_hotmail.com wrote:
>
> > > Hi,
>
> > > I am learning and would need your help.
>
> > > I'm able to create a simple SQL in the oracle view.
>
> > > Select Home
> > > AS
> > > HOME FROM CITYDATABASE
> > > WHERE HOME = 'Single'
> > > /
>
> > > I want to know if there is a possilbe way to add extra column called
> > > "YEAR" like Format([Month_END],"YYYY" into above script.
>
> > > Your help would be much appreciated.
> > > Thanks
>
> > Yes, and it's quite similar to what you typed:
>
> > select home, to_char(month_end, 'YYYY') year
> > from citydatabase
> > where home = 'Simple';
>
> > I don't understand why you alias a column with the same name as the
> > current column name so I didn't do that in my query. And the AS
> > keyword is optional. Note the use of the to_char() function; you can
> > look this up at:
>
> >http://tahiti.oracle.com
>
> > by selecting the release of Oracle you're using and then searching for
> > 'to_char';
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
> ---- learning_co..._at_hotmail.com wrote:
> > On Aug 16, 10:41 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
> > wrote:
> > > On Aug 16, 8:48 am, learning_co..._at_hotmail.com wrote:
>
> > > > Hi,
>
> > > > I am learning and would need your help.
>
> > > > I'm able to create a simple SQL in the oracle view.
>
> > > > Select Home
> > > > AS
> > > > HOME FROM CITYDATABASE
> > > > WHERE HOME = 'Single'
> > > > /
>
> > > > I want to know if there is a possilbe way to add extra column called
> > > > "YEAR" like Format([Month_END],"YYYY" into above script.
>
> > > > Your help would be much appreciated.
> > > > Thanks
>
> > > Yes, and it's quite similar to what you typed:
>
> > > select home, to_char(month_end, 'YYYY') year
> > > from citydatabase
> > > where home = 'Simple';
>
> > > I don't understand why you alias a column with the same name as the
> > > current column name so I didn't do that in my query. And the AS
> > > keyword is optional. Note the use of the to_char() function; you can
> > > look this up at:
>
> > >http://tahiti.oracle.com
>
> > > by selecting the release of Oracle you're using and then searching for
> > > 'to_char';
>
> > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thank you very much It works beautiful. If you don't mind if you
> > can give me the list of codes like "To_char". I'm interesting to look
> > at the average(age) and average(service) group by Home.
>
> If you're planning on sending private email to those responding to you
> DO NOT use some bogus email address as it makes private responses
> impossible. Until you use a valid email account do not email me
> privately.
>
> You're needing to understand the GROUP BY syntax and that's also
> available at the link I provided. I strongly suggest you get
> comfortable with the
> documentation rather than asking more RTFM questions of those kind
> enough to
> give you an answer to one question. The SQL Reference table of
> contents is
> where you need to begin searching:
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Hi David,

Sorry, I have click by accident.

Thanks for guiding me to see the huge list.

It works nicely and I have a question.

I'm trying to set but I kept gettting error.

Select Decode(Grouping(Home),1,'All Homes',Home) as Home Name, Decode(Group(Status),'1','All Status',Satus) as Status Name, Decode(Group(To_Char(Month_End,'YYYY') Year Count(*) "Total",Avg(Cost) "Average Cost"
>From tblHome

Where Home = 'single'
Group By Cube (Home, Status)
Order By Home, Status

I'm getting error because of YEAR. Can you help me? Your help would be much appreciated.

Thanks Received on Fri Aug 17 2007 - 05:01:03 CDT