| sql query help please!! [message #372927] |
Mon, 19 March 2001 00:41  |
david sharon
Messages: 2
Registered: March 2001
|
Junior Member |
|
|
hi. iam facing a problem in sql query can someone help me please.
i have the following tables
1. P_apm_master : e-code,p-code,apm,target
2. p_trans :e-code,apm,actuals
I want the out put as
e_code p_code no of apm's target missedTarget
------ ------ ------------ ------- -----------
i.e i want to count the number of apm's(distinct) and the number of those which have not reached the target(i.e actual < target)
i want to do it in a single query(with any type of subquery)
Can someone help me write the query.It's urgent and iam posting it with real hope that someone would help me.
|
|
|
|
| Re: sql query help please!! [message #372930 is a reply to message #372927] |
Mon, 19 March 2001 04:36  |
Thirumalai and Prakash
Messages: 23
Registered: March 2001
|
Junior Member |
|
|
Hi David,
The following query will do the things what you need.
SELECT e_code,p_code,(SELECT count(*) from p_trans
WHERE e_code = m.e_code) "No of APMs",
(SELECT count(*) from p_trans WHERE
e_code=m.e_code and actual < m.target) "Missed",
(SELECT count(*) from p_trans WHERE e_code=m.e_code and actual >= m.target) "Target"
FROM P_apm_master M ;
This will workout.
Regards
S.Thirumalai
|
|
|
|
| Re: sql query help please!! [message #372931 is a reply to message #372930] |
Mon, 19 March 2001 04:59 |
kshiva
Messages: 10
Registered: March 2001
|
Junior Member |
|
|
i tried using ur query the following error occured
SQL> select count(*),(select totalsuminsured from uwr_selectivepa_det
2 where CompanyCode = A.CompanyCode
3 and FundType = a.FundType
4 And Policyno = A.policyno)
5 ''Sum''
6 from uwr_policy_hdr
7 /
select count(*),(select totalsuminsured from uwr_selectivepa_det A
*
ERROR at line 1:
ORA-00936: missing expression
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
