Home » SQL & PL/SQL » SQL & PL/SQL » dataware house query help !
dataware house query help ! [message #296361] |
Sat, 26 January 2008 03:11 |
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 #296385 is a reply to message #296361] |
Sat, 26 January 2008 08:51 |
|
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
|
|
|
|
|
Goto Forum:
Current Time: Mon Nov 04 06:14:46 CST 2024
|