OraFAQ Forum: SQL & PL/SQL » Can We Club both Query....? Please Help ..

Home » SQL & PL/SQL » SQL & PL/SQL » Can We Club both Query....? Please Help ..

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can We Club both Query....? Please Help .. [message #600699]

Mon, 11 November 2013 06:06

sahnra
Messages: 39
Registered: November 2013
Location: India

Member

Can we club both the red part of the query with If condition as both the query are same but Red part is different.

First Query

Select t1.company, t4.proc_group,
(

select rtrim(t4.first_name)||''||t4.last_name from lawson.zquser t4 where t4.company = t1.company and t4.ZQ_User_ID=t1.service_mgr and t4.position_code= 'BTM')btm,

(
select rtrim(t5.first_name)||''||t5.last_name from lawson.zquser t5 where t5.company = t1.company and t1.hrp = t5.zq_user_id and t5.position_code = 'CSP') csp,

Count (t2.employee)countofemployees,

sum(t3.gross_pay)total_gross_pay
from lawson.comprof t1, lawson.employee t2, lawson.paymastr t3, Lawson.prprocgrp t4

where
t1.company = t2.company and t1.company= t3.company
and t1.process_level= t2.process_level and t1.process_level = t3.process_level and t1.process_level=t4.process_level
and t2.employee=t3.employee
and t1.comp_status= 'A'
and t3.check_date>=sysdate-15
and t1.pay_frequency = 3 --3 means SemimonthlY--
having (sum(t3.gross_pay)>=0 and sum (t3.gross_pay)<=6000)group by t1.company, t4.proc_group,t1.service_mgr,t1.hrp
order by t1.company, t4.proc_group

Second Query

Select t1.gbu, t1.company, t4.proc_group,
(
select rtrim(t5.first_name)||''||t5.last_name from lawson.zquser t5 where t5.company = t1.company and t1.hrp = t5.zq_user_id and t5.position_code = 'CSP') csp,

Count (t2.employee)countofemployees,

sum(t3.gross_pay)total_gross_pay
from lawson.comprof t1, lawson.employee t2, lawson.paymastr t3, Lawson.prprocgrp t4

where
t1.company = t2.company and t1.company= t3.company
and t1.process_level= t2.process_level and t1.process_level = t3.process_level and t1.process_level=t4.process_level
and t2.employee=t3.employee
and t1.comp_status= 'A'

and t3.Per_end_date>=(sysdate-7)
and t1.pay_frequency = 2 --2 means biweekly--
having (sum(t3.gross_pay)>=0 and sum (t3.gross_pay)<=4000)group by t1.gbu,t1.company, t4.proc_group,t1.service_mgr,t1.hrp
order by t1.company, t4.proc_group

Report message to a moderator

Re: Can We Club both Query....? Please Help .. [message #600703 is a reply to message #600699]

Mon, 11 November 2013 06:24

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Try with DECODE; you'd need to know which query you are running (bieweekly or semimonthly).

Report message to a moderator

Re: Can We Club both Query....? Please Help .. [message #600711 is a reply to message #600703]

Mon, 11 November 2013 07:36

sahnra
Messages: 39
Registered: November 2013
Location: India

Member

It not working with Decode

Report message to a moderator

Re: Can We Club both Query....? Please Help .. [message #600714 is a reply to message #600711]

Mon, 11 November 2013 07:59

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Show use the query you tried with decode.

Report message to a moderator

Re: Can We Club both Query....? Please Help .. [message #600716 is a reply to message #600714]

Mon, 11 November 2013 08:21

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

No need to. First query groups by:

group by t1.company, t4.proc_group,t1.service_mgr,t1.hrp

while second by:

group by t1.gbu,t1.company, t4.proc_group,t1.service_mgr,t1.hrp

SY.

Report message to a moderator

Re: Can We Club both Query....? Please Help .. [message #600717 is a reply to message #600714]

Mon, 11 November 2013 08:24

sahnra
Messages: 39
Registered: November 2013
Location: India

Member

SELECT t1.company,
         t4.proc_group,
         (SELECT RTRIM (t4.first_name) || '' || t4.last_name
            FROM lawson.zquser t4
           WHERE     t4.company = t1.company
                 AND t4.ZQ_User_ID = t1.service_mgr
                 AND t4.position_code = 'BTM')
            btm,
         (SELECT RTRIM (t5.first_name) || '' || t5.last_name
            FROM lawson.zquser t5
           WHERE     t5.company = t1.company
                 AND t1.hrp = t5.zq_user_id
                 AND t5.position_code = 'CSP')
            csp,
         COUNT (t2.employee) countofemployees,
         SUM (t3.gross_pay) total_gross_pay
    FROM lawson.comprof t1,
         lawson.employee t2,
         lawson.paymastr t3,
         Lawson.prprocgrp t4
   WHERE     t1.company = t2.company
         AND t1.company = t3.company
         AND t1.process_level = t2.process_level
         AND t1.process_level = t3.process_level
         AND t1.process_level = t4.process_level
         AND t2.employee = t3.employee
         AND t1.comp_status = 'A'
         
         AND Decode( t1.pay_frequency, 3, '6000',
                                       2, '4000',
                                       '')Fall_under


            
   and t3.Per_end_date>=(sysdate-7)
  HAVING (SUM (t3.gross_pay) >= 0 AND SUM (t3.gross_pay) <= 6000)
 
GROUP BY t1.company,
         t4.proc_group,
         t1.service_mgr,
         t1.hrp
ORDER BY t1.company, t4.proc_group

*BlackSwan added {code} tags. PLEASE do so yourself in the future!

[Updated on: Mon, 11 November 2013 08:30] by Moderator

Report message to a moderator

Re: Can We Club both Query....? Please Help .. [message #600718 is a reply to message #600717]

Mon, 11 November 2013 08:37

sahnra
Messages: 39
Registered: November 2013
Location: India

Member

while running this query showing Error ORA-00979: not a GROUP BY expression

SELECT t1.company, Decode( t1.pay_frequency, 3, '6000' , 2, '4000','t1.pay_frequency')Fullsd ,

t4.proc_group,
(SELECT RTRIM (t4.first_name) || '' || t4.last_name
FROM lawson.zquser t4
WHERE t4.company = t1.company
AND t4.ZQ_User_ID = t1.service_mgr
AND t4.position_code = 'BTM')
btm,
(SELECT RTRIM (t5.first_name) || '' || t5.last_name
FROM lawson.zquser t5
WHERE t5.company = t1.company
AND t1.hrp = t5.zq_user_id
AND t5.position_code = 'CSP')
csp,
COUNT (t2.employee) countofemployees,
SUM (t3.gross_pay) total_gross_pay
FROM lawson.comprof t1,
lawson.employee t2,
lawson.paymastr t3,
Lawson.prprocgrp t4
WHERE t1.company = t2.company
AND t1.company = t3.company
AND t1.process_level = t2.process_level
AND t1.process_level = t3.process_level
AND t1.process_level = t4.process_level
AND t2.employee = t3.employee
AND t1.comp_status = 'A'

and t3.Per_end_date>=(sysdate-7)
HAVING (SUM (t3.gross_pay) >= 0 AND SUM (t3.gross_pay) <= 6000)

GROUP BY t1.gbu,t1.company, t4.proc_group,t1.service_mgr,t1.hrp

ORDER BY t1.company, t4.proc_group

[Updated on: Mon, 11 November 2013 08:40]

Report message to a moderator