MVSFORUMS.com :: View topic - SQL Query needed

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

SQL Query needed
Goto page Previous 1, 2

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12398
Topics: 75
Location: San Jose

Posted: Thu Jun 02, 2005 2:33 pm Post subject:

Rahul,

Try this Sql. I assumed that your ACT_NO column in table CCCC is defined as Smallint/integer.

Code:

SELECT E.*
FROM (SELECT BBBB.POLICY_NO
,D.ACT_NO ACT_NO
,SUM(BBBB.BASIC_AMT + BBBB.CHARG_AMT) +
SUM(D.T_AMT) / SUM(D.T_CNT) TOTAL
FROM BBBB
,(SELECT CCCC.POLICY_NO
,CCCC.ACT_NO
,SUM(AAAA.TRAN_AMT) T_AMT
,INT(1) T_CNT
FROM CCCC
,AAAA
WHERE AAAA.STAT_TYPE = 'OK'
AND CCCC.ACT_NO = AAAA.ACT_NO
GROUP BY CCCC.POLICY_NO
,CCCC.ACT_NO) D
WHERE BBBB.POLICY_NO = D.POLICY_NO
GROUP BY BBBB.POLICY_NO
,D.ACT_NO
UNION
SELECT A.POLICY_NO
,INT(0) ACT_NO
,A.T_AMT TOTAL
FROM (SELECT POLICY_NO
,SUM(BASIC_AMT + CHARG_AMT) T_AMT
FROM BBBB
WHERE POLICY_NO NOT IN (SELECT POLICY_NO
FROM CCCC)

GROUP BY POLICY_NO) A ) E
WHERE E.TOTAL < 0
;

Hope this helps...

Cheers

Kolusu

Ps: I ain't gonna explain the query for you. Try to understand the new query after the UNION clause. It is actually simple query.
_________________
Kolusu
www.linkedin.com/in/kolusu

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours Goto page Previous 1, 2
Page 2 of 2

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum