Home » SQL & PL/SQL » SQL & PL/SQL » dataware house query help !
dataware house query help ! [message #296361] Sat, 26 January 2008 03:11 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi pals,

I need some help from u.
This is datawarehousing related stuff.
I am having a source table as "test" and target table as "trg".
I need to extract the data in required format as per below loading instructions and then load the data into "trg" table.
Below sample data is only given one zipcode.There can be several codes.


drop table test

create table test
(
currentyear int,
district varchar(10),
school varchar(10),
rollno int,
zipcode varchar(10),
flag1_handicapped char(1),
flag2_disadvantaged char(1),
status varchar(10),
relation varchar(10)
)
/* inserted 11 rows */
insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','R')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','R')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR')
insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','NR')

select * from test





--- Structure of the target table
create table trg
(
cyear int,
district varchar(10),
school varchar(10),
RollNo int,
zipcode varchar(10),
type varchar(20), /* This is an extra column with hard coded values which we need to assume as Total,flag1_handicapped,flag2_Disadvantaged.For Every unique zipcode i need to GROUP BY these 3 values.
These values never come from the source table i.e "test".But we can make use of the 2 source columns "flag1_handicapped" & "flag2_Disadvantaged"*/
actaul_cnt int,
empl_related int,
empl_not_related int,
modified_date date
)

-- The below table shows what values should get loaded into trg table

----------------------------------------------------------------------
trg table column value to be loaded Description
-----------------------------------------------------------------------
cyear test.currentyear
district test.district
school test.school
rollno test.rollno
zipcode test.zipcode
type /* here we to load 3 rows with 3 values
This table contains some calculated columns. such as "actual_cnt","empl_related","empl_not_related" and so on...
Every calculation should be grouped by this "type" column.For reference the you can see the bottom output rows how they should look like.
The 3 valid values for this type column is "Total","flag1_handicapped","Disadavantaged".
"Total" means = All the records which satisfies the calculation.
"flag1_handicapped" means = All the records which statisfies the calculation and have test.flag1_handicapped = 'Y'
"flag2_Disadvantaged" means = All the records which satisfies the calculation and have test.disadvanatged = 'Y'*/


actaul_cnt This is a calculated column. The calc is as follows:
count of records grouped by currentyear,district,school,zipcode,type(flag1_handicapped,flag2_Disadvantaged,total)

empl_related This is again calculated column. The calc is as follows.
count of records where status='E' and relation = 'R' grouped by currentyear,district,school,zipcode,type(flag1_handicapped,flag2_Disadvantaged,total)

empl_not_related This is again calculated column. The calc is as follows.
count of records where status='E' and relation = 'NR' grouped by currentyear,district,school,zipcode,type(flag1_handicapped,flag2_Disadvantaged,total)


modified_date sysdate

-----------------------------------------------------------------------------------------------


Here is the sample template which i felt like using to load the data. We need to modify this query littlt bit accordingly as per above rules.

select
currentyear as "CYear",
district as "District",
school as "School",
rollno as "RollNo",
zipcode as "zipcode",
count(*) "actaul_count",
sum(case when (status='E' and relation='R') then 1 else 0 end) "Emp_Related",
sum(case when (status='E' and relation='NR') then 1 else 0 end) "Emp_Not_Related",
getdate() "Date"
from test
group by currentyear,
district,
school,
rollno,
zipcode

/* Using the above query we need to load 3 rows into below target table whose structure is defined as follows */



------------------------------------------------------------------------------------------------------------------------

Expected Output Rows using above sample data
----------------------------------------------------
CYEAR|DISTRICT|SCHOOL|ROLLNO|ZIPCDE| TYPE |ACTUALCOUNT| EMPL_RELATED |EMPL_NOT_RELATED |MODIFIED_DT
-------------------------------------------------------------------------------------------------------------------------
2005 | D1 | S1 | 101 | 530024 | Total | 11 | 2 | 9 | 2002-01-26
2005 | D1 | S1 | 101 | 530024 | flag1_handicapped | 2 | 1 | 1 | 2002-01-26
2005 | D1 | S1 | 101 | 530024 | flag2_Disadvantaged | 2 | 1 | 1 | 2002-01-26



------------------------------------------------------------------------------------------------------------------------
But using above SELECT,i am able to get only row as output that to i am not able to show the "type" column in the output

2005 | D1 | S1 | 101 | 530024 | 11 | 2 | 1 | 2002-01-26 12:57:53.420 |


------------------------------------------------------------------------------------------------------------------------
Basically i am not getting how to build the Group by clause and displaying the type code using above rules.
Can anyone help me out in solving the problem.
Do we need to perform any UNION ALL ON test.flag1_handicapped and test.flag2_Disadvantaged columns.?
This is totally seems out of box for me.

Any help would be greatly appreciated.


Thanks in Advance.
Re: dataware house query help ! [message #296370 is a reply to message #296361] Sat, 26 January 2008 05:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

If i am not mistaken about your reqirement , you need to use
UNION ALL for getting three Records for single Group by .

Thumbs Up
Rajuvan
Re: dataware house query help ! [message #296385 is a reply to message #296361] Sat, 26 January 2008 08:51 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
162 post and you don't know how to post. I think I don't know how to answer.

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Oracle Sample Schema's?
Next Topic: select 1
Goto Forum:
  


Current Time: Mon Nov 04 06:14:46 CST 2024