| What wroung i am doing In this query Please suggest [message #600694] |
Mon, 11 November 2013 05:36  |
 |
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
CASE
WHEN (
t3.check_date>=sysdate-15
and t1.pay_frequency = 3 ) then
'6000'
WHEN
(
t1.pay_frequency = 2
and t3.check_date>=sysdate-7
)
then '4000'
end "Gross_pAY"
and t1.comp_status= 'A'
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
|
|
|
|
|
|
|
|
| Re: What wroung i am doing In this query Please suggest [message #600702 is a reply to message #600700] |
Mon, 11 November 2013 06:23  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This is your query, formatted. Two lines are commented: one is "Gross_pAY" label (which should not be used here); another one is superfluous closing bracket. Try to run it again and see what happens.
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 CASE
WHEN (t3.check_date >= SYSDATE - 15 AND t1.pay_frequency = 3)
THEN
'6000'
WHEN (t1.pay_frequency = 2 AND t3.check_date >= SYSDATE - 7)
THEN
'4000'
END
-- "Gross_pAY" --> commented!
AND t1.comp_status = 'A'
HAVING (SUM (t3.gross_pay) >= 0 AND SUM (t3.gross_pay) <= 6000)
--) --> commented!
GROUP BY t1.company,
t4.proc_group,
t1.service_mgr,
t1.hrp
ORDER BY t1.company, t4.proc_group |
|
|
|
|
|
|
|
| Re: What wroung i am doing In this query Please suggest [message #600708 is a reply to message #600707] |
Mon, 11 November 2013 07:11 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Again, your condition is:
AND x
which makes no sense. And I have no clue what you mean by "Value 4000 and 6000 are also to be stored into a new column how we can do that.." You have WHERE clause condition. What it has to do whit storing new column? Anyway, based on your "Can we club" post, I'd guess you want:
AND t3.gross_pay <= CASE
WHEN (t3.check_date >= SYSDATE - 15 AND t1.pay_frequency = 3)
THEN
'6000'
WHEN (t1.pay_frequency = 2 AND t3.check_date >= SYSDATE - 7)
THEN
'4000'
END
If not, you need to explain in wirds what thi condition is supposed to check.
SY.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
