Confused...

[digimortal]

Hi all, I'm writing a little bit long select query;

SELECT   I.ACCOUNT, C.NAME1 AS STEXT,C.COUNTRY, 
   (CASE WHEN EXTPAYDOCNUM IS NOT NULL AND PAYMDOCKEY = '3' THEN
	NVL((SELECT SUM(HPOSTAMNT * (POSTWAY * 2 - 1)) AS OPENAMNT1
        FROM IASFINITEM
        WHERE (CLIENT = '00') AND (COMPANY = '01') AND (ACCTYPE = 'T') AND (ACCOUNT = I.ACCOUNT) AND (FINYEAR = '2005') AND (POSTDATE < '19.07.2005')),0)
	ELSE
	NVL((SELECT SUM(HPOSTAMNT * (POSTWAY * 2 - 1)) AS OPENAMNT1
        FROM IASFINITEM
        WHERE (CLIENT = '00') AND (COMPANY = '01') AND (ACCTYPE = 'T') AND (ACCOUNT = I.ACCOUNT) AND (FINYEAR = '2005') AND (DUEDATE < '19.07.2005')),0)
	END)
	AS GECIKEN,
	
   (CASE WHEN EXTPAYDOCNUM IS NOT NULL AND PAYMDOCKEY = '3' THEN
	NVL((SELECT SUM(HPOSTAMNT * (POSTWAY * 2 - 1)) AS OPENAMNT2
		FROM IASFINITEM
		WHERE (CLIENT = '00') AND (COMPANY = '01') AND (ACCTYPE = 'T') AND (ACCOUNT = I.ACCOUNT) AND (FINYEAR = '2005') AND (POSTDATE > '19.07.2005')),0) 
	ELSE
	NVL((SELECT SUM(HPOSTAMNT * (POSTWAY * 2 - 1)) AS OPENAMNT2
		FROM IASFINITEM
		WHERE (CLIENT = '00') AND (COMPANY = '01') AND (ACCTYPE = 'T') AND (ACCOUNT = I.ACCOUNT) AND (FINYEAR = '2005') AND (DUEDATE > '19.07.2005')),0)
	END)
	AS GELMEYEN,
	
	NVL((SELECT SUM(((2 * I4.POSTWAY) - 1) * I4.HPOSTAMNT) AS BORC
	 FROM IASFINITEM I4
	 WHERE I4.CLIENT = I.CLIENT AND I4.COMPANY = I.COMPANY AND I4.ACCOUNT = I.ACCOUNT AND I4.ACCTYPE = I.ACCTYPE),0) AS BAKIYE, 
	 
	 
	 X.STEXT AS GRUP_ADI, GA.GLACCOUNT, C.ISCLIENT


FROM 	IASFIN004 A, IASFINITEM I, IASFINHEAD H, IASCUSTOMER C, IASACCOUNT GA, IASACCOUNTX X
WHERE 	A.CLIENT = '00' AND A.COMPANY = '01' AND A.ACCIND = 1
	AND I.CLIENT = A.CLIENT
	AND I.COMPANY = A.COMPANY
.
.
.
.
.
	AND GA.GLACCOUNT <> '320-14'
GROUP BY I.CLIENT,I.COMPANY,A.ACCIND,I.ACCTYPE,I.ACCOUNT , C.NAME1, C.COUNTRY, GA.GLACCOUNT, X.STEXT, C.ISCLIENT
HAVING 
NVL((SELECT 	SUM(((2 * I4.POSTWAY) - 1) * I4.HPOSTAMNT) AS BORC FROM IASFINITEM I4
	 WHERE  	I4.CLIENT = I.CLIENT AND I4.COMPANY = I.COMPANY	AND I4.ACCOUNT = I.ACCOUNT AND I4.ACCTYPE = I.ACCTYPE),0) > 0 
	 ORDER BY GA.GLACCOUNT, C.NAME1

But it gives an error at the first extpaydocnum field (in the case statement). But when I use something like sum() or max() like;

   MAX(CASE WHEN EXTPAYDOCNUM IS NOT NULL AND PAYMDOCKEY = '3' THEN
	NVL((SELECT SUM(HPOSTAMNT * (POSTWAY * 2 - 1)) AS OPENAMNT1
        FROM IASFINITEM
        WHERE (CLIENT = '00') AND (COMPANY = '01') AND (ACCTYPE = 'T') AND (ACCOUNT = I.ACCOUNT) AND (FINYEAR = '2005') AND (POSTDATE < '19.07.2005'))

it doesnt give any error but the values change... and I can not figure out why do I have to use a grouping function for a case in a select.

Thanx in advance...

 

[lewisp]

Go on then, give us a clue... Whats the error? ;-)