Home » SQL & PL/SQL » SQL & PL/SQL » sql query proposed for using case (oracle 10g)
sql query proposed for using case [message #427684] Sat, 24 October 2009 01:24 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi All
Can anyone help me here

This code works fine,here inthe inner sub queries(b,c,d,e,f),i am getting the weekly counts of usage data from the table mf_wer_OBI_USAGE_reqq.

As this is hitting same table with the similar set of queries so i was adviced to use case statement by taking the wk_1...5 in variable and making the query better

I am unable to figure out how to proceed.

Appreciate your help here.

Thanks

create table mf_wer_OBI_USAGE_reqq_WK
as select x.user_name id,x.mon MONTH_COUNT,
x.wk_1 WEEK1_COUNT,x.wk_2 WEEK2_COUNT,x.wk_3 WEEK3_COUNT,
x.wk_4 WEEK4_COUNT,x.wk_5 WEEK5_COUNT,x.subject_area_name,
y.EMP_FIRST_NAME FIRSTNAME,y.EMP_LAST_NAME SURNAME,y.E_MAIL_ADDRESS USER_MAILID,y.ouc OUC 
from (select a.user_name,a.mon,a.subject_area_name,b.wk_1,c.wk_2,d.wk_3,e.wk_4,f.wk_5 
from (select user_name,sum(count_us_st) mon,subject_area_name 
from mf_wer_OBI_USAGE_reqq group by user_name,subject_area_name) a,
(select user_name,sum(count_us_st) wk_1,subject_area_name 
from mf_wer_OBI_USAGE_reqq where extract(day from start_dt) between 1 and 7
group by user_name,subject_area_name) b,
(select user_name,sum(count_us_st) wk_2,subject_area_name 
from mf_wer_OBI_USAGE_reqq where extract(day from start_dt) between 8 and 14
group by user_name,subject_area_name) c,
(select user_name,sum(count_us_st) wk_3,subject_area_name 
from mf_wer_OBI_USAGE_reqq where extract(day from start_dt) between 15 and 21
group by user_name,subject_area_name) d,
(select user_name,sum(count_us_st) wk_4,subject_area_name 
from mf_wer_OBI_USAGE_reqq where extract(day from start_dt) between 22 and 28
group by user_name,subject_area_name) e,
(select user_name,sum(count_us_st) wk_5,subject_area_name 
from mf_wer_OBI_USAGE_reqq where extract(day from start_dt) between 29 and 31
group by user_name,subject_area_name) f
where a.user_name=b.user_name(+)
and a.subject_area_name=b.subject_area_name(+)
and a.user_name=c.user_name(+)
and a.subject_area_name=c.subject_area_name(+)
and a.user_name=d.user_name(+)
and a.subject_area_name=d.subject_area_name(+)
and a.user_name=e.user_name(+)
and a.subject_area_name=e.subject_area_name(+)
and a.user_name=f.user_name(+)
and a.subject_area_name=f.subject_area_name(+)) x,
dm_employee y
where x.user_name=y.id and
y.active_flg='Y';

[Updated on: Sat, 24 October 2009 01:27] by Moderator

Report message to a moderator

Re: sql query proposed for using case [message #427689 is a reply to message #427684] Sat, 24 October 2009 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your query and do not exceed 80 characters in each line.
Waiting for your repost.

Regards
Michel
Re: sql query proposed for using case [message #427732 is a reply to message #427689] Sat, 24 October 2009 10:25 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi Miechel

Reposting the query

Thanks

CREATE TABLE mf_wer_OBI_USAGE_reqq_WK AS
SELECT x.user_name                    id         ,
       x.mon                          MONTH_COUNT,
       x.wk_1                         WEEK1_COUNT,
       x.wk_2                         WEEK2_COUNT,
       x.wk_3                         WEEK3_COUNT,
       x.wk_4                         WEEK4_COUNT,
       x.wk_5                         WEEK5_COUNT,
       x.subject_area_name                       ,
       y.EMP_FIRST_NAME FIRSTNAME                ,
       y.EMP_LAST_NAME  SURNAME                  ,
       y.E_MAIL_ADDRESS USER_MAILID              ,
       y.ouc            OUC
FROM   (SELECT a.user_name        ,
               a.mon              ,
               a.subject_area_name,
               b.wk_1             ,
               c.wk_2             ,
               d.wk_3             ,
               e.wk_4             ,
               f.wk_5
       FROM    (SELECT  user_name           ,
                        SUM(count_us_st) mon,
                        subject_area_name
               FROM     mf_wer_OBI_USAGE_reqq
               GROUP BY user_name,
                        subject_area_name
               )
               a                             ,
               (SELECT  user_name            ,
                        SUM(count_us_st) wk_1,
                        subject_area_name
               FROM     mf_wer_OBI_USAGE_reqq
               WHERE    extract(DAY FROM start_dt) BETWEEN 1 AND      7
               GROUP BY user_name,
                        subject_area_name
               )
               b                             ,
               (SELECT  user_name            ,
                        SUM(count_us_st) wk_2,
                        subject_area_name
               FROM     mf_wer_OBI_USAGE_reqq
               WHERE    extract(DAY FROM start_dt) BETWEEN 8 AND      14
               GROUP BY user_name,
                        subject_area_name
               )
               c                             ,
               (SELECT  user_name            ,
                        SUM(count_us_st) wk_3,
                        subject_area_name
               FROM     mf_wer_OBI_USAGE_reqq
               WHERE    extract(DAY FROM start_dt) BETWEEN 15 AND      21
               GROUP BY user_name,
                        subject_area_name
               )
               d                             ,
               (SELECT  user_name            ,
                        SUM(count_us_st) wk_4,
                        subject_area_name
               FROM     mf_wer_OBI_USAGE_reqq
               WHERE    extract(DAY FROM start_dt) BETWEEN 22 AND      28
               GROUP BY user_name,
                        subject_area_name
               )
               e                             ,
               (SELECT  user_name            ,
                        SUM(count_us_st) wk_5,
                        subject_area_name
               FROM     mf_wer_OBI_USAGE_reqq
               WHERE    extract(DAY FROM start_dt) BETWEEN 29 AND      31
               GROUP BY user_name,
                        subject_area_name
               )
               f
       WHERE   a.user_name        =b.user_name(+)
       AND     a.subject_area_name=b.subject_area_name(+)
       AND     a.user_name        =c.user_name(+)
       AND     a.subject_area_name=c.subject_area_name(+)
       AND     a.user_name        =d.user_name(+)
       AND     a.subject_area_name=d.subject_area_name(+)
       AND     a.user_name        =e.user_name(+)
       AND     a.subject_area_name=e.subject_area_name(+)
       AND     a.user_name        =f.user_name(+)
       AND     a.subject_area_name=f.subject_area_name(+)
       )
       x,
       dm_employee y
WHERE  x.user_name =y.id
AND    y.active_flg='Y';
Re: sql query proposed for using case [message #427737 is a reply to message #427732] Sat, 24 October 2009 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use
sum(case when extract ... end case)
for each week and only one subquery without any restriction on day.

Regards
Michel
Re: sql query proposed for using case [message #427766 is a reply to message #427737] Sun, 25 October 2009 03:58 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member

Hi Is this what you mean...


select user_name,
       sum(count_us_st) mon,
       subject_area_name,
       sum(case when extract(day from start_dt) between 1 and 7
                then count_us_st
           end
          ) wk1,
       sum(case when extract(day from start_dt) between 8 and 14
                then count_us_st
           end
          ) wk2,
       sum(case when extract(day from start_dt) between 15 and 21
                then count_us_st
           end
          ) wk3,
       sum(case when extract(day from start_dt) between 22 and 28
                then count_us_st
           end
          ) wk4,
       sum(case when extract(day from start_dt) between 29 and 31
                then count_us_st
           end
          ) wk5
  from mf_wer_OBI_USAGE_reqq 
 group by user_name,subject_area_name
Re: sql query proposed for using case [message #427773 is a reply to message #427766] Sun, 25 October 2009 06:28 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, something like that.

Regards
Michel
Previous Topic: how to set the server time in sql prompt
Next Topic: How to get all the expensive/costly query
Goto Forum:
  


Current Time: Sun Sep 25 12:57:52 CDT 2016

Total time taken to generate the page: 0.09629 seconds