akashprashant
New User
Joined: 31 Jul 2007
Posts: 1
Location: New Delhi
|
|
|
|
Hi!
The following query is returning SQLCODE=-122. Do I need to add all the columns that I am SELECTing in the GROUP BY clause?
SELECT C20.PRSN_INTN_ID
,SUM(C20.SMRY_BAL_UT)
,C20.SMRY_THRU_PRCS_DT
,VALUE (AAA.UTAFTER,0)
,A31.PLAN_ID
,A31.ACCT_ID
,A31.FUND_ID
,A31.SF_ID
,A22.EM_CLNT_ID
,VALUE (A26.ORG_CD,' ')
,VALUE (A04.EMPL_STAT_CD,' ')
,VALUE (A56.PLAN_STAT_CD,' ')
FROM PRSN_SMRY_HBAL C20
LEFT JOIN
(SELECT PRSN_INTN_ID
,HBAL_ID
,SUM(BAL_UT) AS UTAFTER
FROM PRSN_HBAL A52A
WHERE PRCS_DT > :Host-EfEndDt
GROUP BY PRSN_INTN_ID
,HBAL_ID
) AAA
ON AAA.PRSN_INTN_ID = C20.PRSN_INTN_ID
AND AAA.HBAL_ID = C20.HBAL_ID
INNER JOIN
HBAL A31
ON A31.HBAL_ID = C20.HBAL_ID
INNER JOIN
PRSN A22
ON A22.PRSN_INTN_ID = C20.PRSN_INTN_ID
LEFT JOIN
EE_ORG A26
ON A26.PRSN_INTN_ID = C20.PRSN_INTN_ID
AND A26.ORG_CAT_ID = :A26-ORG-CAT-ID
AND EE_ORG_EFBEGDT <= :Host-EfEndDt
AND EE_ORG_EFENDDT >= :Host-EfEndDt
LEFT JOIN
EE_EMPL_CAT A04
ON A04.PRSN_INTN_ID = C20.PRSN_INTN_ID
AND A04.EMPL_CAT_ID = :A04-EMPL-CAT-ID
AND EE_EMPLCAT_EFBEGDT <= :Host-EfEndDt
AND EE_EMPLCAT_EFENDDT >= :Host-EfEndDt
LEFT JOIN
PP_STAT A56
ON A56.PRSN_INTN_ID = C20.PRSN_INTN_ID
AND A56.PLAN_STAT_CAT_ID = :A56-PLAN-STAT-CAT-ID
AND EE_EMPLCAT_EFBEGDT <= :Host-EfEndDt
AND EE_EMPLCAT_EFENDDT >= :Host-EfEndDt
GROUP BY AAA.HBAL_ID
,C20.PRSN_INTN_ID
,C20.SMRY_BAL_UT
ORDER BY A31.PLAN_ID
WITH UR |
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
yes, unless you use the same trick as you did for subselect AAA
| Code: |
...
FROM (select PRSN_INTN_ID,SMRY_THRU_PRCS_DT
, sum(C20.SMRY_BAL_UT) as UTbefore from PRSN_SMRY_HBAL
group by PRSN_INTN_ID,SMRY_THRU_PRCS_DT) C20
LEFT JOIN ... |
|
|
