problem creating view...if someone can help

[sourabhjha]

I have a table with four columns in it.One of them is a date field,two chars and one number
I want to create a view with following fields(same four)
-two char fields mentioned above but with all the distinct combinations of them along with the month-year value of the date field.So date field will now contain only the month-year value and the two chars will have only the distinct combinations for every month-year.
-Number fields sum totalled for each month-year

To give an example
MyTable
field1 field2 field3 field4
a1 b1 1 2nd Jan,2001
a1 b1 7 3nd Jan,2001
a2 b2 4 2nd Jan,2001
a2 b2 5 2nd Jan,2001
a3 b3 2 2nd feb,2001
a3 b3 3 3nd feb,2001
a4 b1 6 2nd feb,2001
a4 b1 1 2nd feb,2001
MyView
field1 field2 field3 field4
a1 b1 8 Jan,2001
a2 b2 9 Jan,2001
a3 b3 5 Feb,2001
a4 b1 7 Feb,2001

So my problem is to create the view MyView

Thanks in advance
-Sourabh

 

[JamesGordon]

create or replace view myview as
select field1, field2, sum(field3) field3, min(to_char(field4, 'MON-YY')) field4
from mytable
group by field1, field2;

 

[sourabhjha]

Thanks a lot James....It will really help if you could also tell me the syntax for having the view with same conditions but the date field criteria being
-week-year
or
-quaterly-year
or
-year

 

[JamesGordon]

Have a look through the docs for to_char function manipluating dates. to_char(xxx, 'YYYY') will give 2002, to_char(xxx, 'IW YYYY') gives 12 2002 (12 being the week number).

 

[sourabhjha]

Hi James,
With reference to the solution u had given for creating the view,i am not getting the desired result.Could you please tell me that in the query
"CREATE OR REPLACE VIEW MYVIEW AS SELECT PRODUCT_NAME,REGION_NAME,SUM(UNITSSOLD) UNITSSOLD ,MIN(TO_CHAR(DATEOFSALE,'MON-YY')) DATEOFSALE FROM ALLSALESDATA GROUP BY REGION_NAME,PRODUCT_NAME;"
Will i get the desired view.
My aim is to get monthly total UNITSSOLD values for distinct REGION_NAME-PRODUCT_NAME combinations .In fact i am not getting the desired result.So like a given REGION_NAME-PRODUCT_NAME combination if i have 3 records each for Jan Feb and MArch then in the view i shall get three records(one for Jan ,Feb and March each) with unitsold values totallled for each month.

 

[JamesGordon]

Yep! It works for me. I've inputted the data above into my DB and created the view and I get the results you wanted.
What is it you are getting instead?

 

[sourabhjha]

i will give a sample MyTable records and Desired MyView Records once more.This time the data is a bit more widespread
Records of MyTable
field1 field2 field3 field4
a1 b1 1 2nd Jan,2001
a1 b1 7 3nd Jan,2001
a1 b1 4 4nd Feb,2001
a1 b1 5 5nd Feb,2001
a1 b1 2 7nd Mar,2001
a1 b1 3 1nd Mar,2001
a2 b2 6 11nd Jan,2001
a2 b2 3 12nd Jan,2001
a2 b2 9 14nd Feb,2001
a2 b2 1 21nd Feb,2001
a2 b2 4 25nd Mar,2001
a2 b2 7 20nd Mar,2001
a2 b2 6 2nd Apr,2001

Exprected MyView Records:
field1 field2 field3 field4
a1 b1 8 Jan,2001
a1 b1 9 Feb,2001
a1 b1 5 Mar,2001
a2 b2 9 Jan,2001
a2 b2 10 Feb,2001
a2 b2 11 Mar,2001
a2 b2 6 Apr,2001

Just check the sql with these records

 

[sourabhjha]

Hi James,
Ignore my previous query.The problem is solved now...inthe GROUP BY clause "to_char(dateofsale,'mom-yyyy')" had to be added.
Thnaks a lot
-Sourabh

 

[sourabhjha]

Hi James,
one problem regarding that view.If u remember the query u had suggested was
"create or replace view MyView as
select field1, field2, sum(field3) field3, min(to_char(field4, 'MON-YY')) field4
from mytable group by field1, field2;"
which is perfectly okay .i just modified it to
"create or replace view myview as select field1, field2, sum(field3) field3, to_char(field4, 'MON-YY') field4 from mytable group by field1, field2 ,to_char(field4, 'MON-YY')"
to get the desired result.
But the problem here is that suppose i fire a query something like this:
"Select * from MyView order by field4" then i get records with field4 sorted alphabetically.field4 earlier was a date field.So i want it to be sorted by date.As we applied to_char function to the date field so it is being sorted alphabatically .
How to get away with this problem......how is it possible to fire queries on MyView an getting records sorted by date.
Is it possible by some function in oracle which converts string to date type ...something like "to_date" if it exists...
-Sourabh