SQL does not substract

Well where to start.
Your query may work but it is dificult to check without all of the other stuff around it (tables, data, stuff like that).

To check the code I expanded it to use a UNION ALL and split the command into a query that deals with the special points and another that does the normal points. On top of these two is a query that groups, merges and orders the resultant data. Will this work? I've no idea. I have nothing to test it on.

So cut and paste this into a query analyser window and run it. At least you will be able to test the individual parts of the query and thus find the problem

SELECT X.CLIENT AS CLIENT,
SUM(X.ACTUAL_POINTS) AS ACTUAL_POINTS,
SUM(X.NORMAL_AMMOUNT) AS NORMAL_AMMOUNT,
SUM(X.SPECIAL_AMMOUNT) AS SPECIAL_AMMOUNT,
SUM(X.TOTAL_AMMOUNT) AS TOTAL_AMMOUNT,
SUM(X.NORMAL_POINTS) AS NORMAL_POINTS,
SUM(X.SPECIAL_POINTS) AS SPECIAL_POINTS,
SUM(X.TOTAL_POINTS) AS TOTAL_POINTS,
SUM(X.EXCHANGED_POINTS) AS EXCHANGED_POINTS

FROM (SELECT
C.NAME AS CLIENT,
SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS) AS ACTUAL_POINTS,
0 AS NORMAL_AMMOUNT,
SUM(I.AMMOUNT) AS SPECIAL_AMMOUNT,
SUM(I.AMMOUNT) AS TOTAL_AMMOUNT,
0 AS NORMAL_POINTS,
SUM(INVOICE.POINTS) AS SPECIAL_POINTS,
SUM(INVOICE.POINTS) AS TOTAL_POINTS,
SUM(EXCHANGE.TOTALPOINTS) AS EXCHANGED_POINTS
FROM CLIENT C,
INVOICE I,
EXCHANGE E
WHERE C.ID = I.IDCLIENT
AND I.IS_SPECIAL_PROMO = TRUE
AND C.ID = E.IDCLIENT
AND C.IDPROMO = 1
-- I replace "1" by :PARAM_ID_NORMALPROMOTION

UNION ALL

SELECT
C.NAME AS CLIENT,
SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS) AS ACTUAL_POINTS,
SUM(INVOICE.AMMOUNT) AS NORMAL_AMMOUNT,
0 AS SPECIAL_AMMOUNT,
SUM(I.AMMOUNT) AS TOTAL_AMMOUNT,
SUM(I.POINTS) AS NORMAL_POINTS,
0 AS SPECIAL_POINTS,
SUM(INVOICE.POINTS) AS TOTAL_POINTS,
SUM(EXCHANGE.TOTALPOINTS) AS EXCHANGED_POINTS
FROM CLIENT C,
INVOICE I,
EXCHANGE E
WHERE C.ID = I.IDCLIENT
AND I.IS_SPECIAL_PROMO = FALSE
AND C.ID = E.IDCLIENT
AND C.IDPROMO = 1) X
-- I replace "1" by :PARAM_ID_NORMALPROMOTION
GROUP BY X.NAME, X.ACTUAL_POINTS
ORDER BY X.NAME

Finally I cannot stress the importance of correctly formated code. I don't care which standard you adhere to as long as the code is indented. It makes the code MUCH easier to read for others and for yourself after a couple of months have past since you last looked at it

Final Final note.

I've just read this back AFTER posting it. All my lovely indentation is gone. So cut and paste the code, work on it a bit and it should do what you want.

Hi pclfw
1º of all, Thanks a lot for helping.

You know, i pasted your sql code, and I got this error:

I think the cause is the SQL engine I use -> www.nexusdb.com

I could post the server, manager, and db files for live testing, but it is lot of work, and don't want to take you lot of time...

This is the error I got: Non-aggregate column "C.NAME AS CLIENT" must appear in GROUP BY

:rolleyes:

Hmoner,

OK,

Back to basics.

You need to split the code I gave you down to its component parts.

First try this section

SELECT
C.NAME AS CLIENT,
SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS) AS ACTUAL_POINTS,
0 AS NORMAL_AMMOUNT,
SUM(I.AMMOUNT) AS SPECIAL_AMMOUNT,
SUM(I.AMMOUNT) AS TOTAL_AMMOUNT,
0 AS NORMAL_POINTS,
SUM(INVOICE.POINTS) AS SPECIAL_POINTS,
SUM(INVOICE.POINTS) AS TOTAL_POINTS,
SUM(EXCHANGE.TOTALPOINTS) AS EXCHANGED_POINTS
FROM CLIENT C,
INVOICE I,
EXCHANGE E
WHERE C.ID = I.IDCLIENT
AND I.IS_SPECIAL_PROMO = TRUE
AND C.ID = E.IDCLIENT
AND C.IDPROMO = 1

This is the special points ammount code.
Does this do what you expect?

If it does then try then next bit, the normal points section

SELECT
C.NAME AS CLIENT,
SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS) AS ACTUAL_POINTS,
SUM(INVOICE.AMMOUNT) AS NORMAL_AMMOUNT,
0 AS SPECIAL_AMMOUNT,
SUM(I.AMMOUNT) AS TOTAL_AMMOUNT,
SUM(I.POINTS) AS NORMAL_POINTS,
0 AS SPECIAL_POINTS,
SUM(INVOICE.POINTS) AS TOTAL_POINTS,
SUM(EXCHANGE.TOTALPOINTS) AS EXCHANGED_POINTS
FROM CLIENT C,
INVOICE I,
EXCHANGE E
WHERE C.ID = I.IDCLIENT
AND I.IS_SPECIAL_PROMO = FALSE
AND C.ID = E.IDCLIENT
AND C.IDPROMO = 1

if this works as you expect then put the two together with a UNION ALL. Does this work OK?

SELECT
C.NAME AS CLIENT,
SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS) AS ACTUAL_POINTS,
0 AS NORMAL_AMMOUNT,
SUM(I.AMMOUNT) AS SPECIAL_AMMOUNT,
SUM(I.AMMOUNT) AS TOTAL_AMMOUNT,
0 AS NORMAL_POINTS,
SUM(INVOICE.POINTS) AS SPECIAL_POINTS,
SUM(INVOICE.POINTS) AS TOTAL_POINTS,
SUM(EXCHANGE.TOTALPOINTS) AS EXCHANGED_POINTS
FROM CLIENT C,
INVOICE I,
EXCHANGE E
WHERE C.ID = I.IDCLIENT
AND I.IS_SPECIAL_PROMO = TRUE
AND C.ID = E.IDCLIENT
AND C.IDPROMO = 1
-- I replace "1" by :PARAM_ID_NORMALPROMOTION

UNION ALL

SELECT
C.NAME AS CLIENT,
SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS) AS ACTUAL_POINTS,
SUM(INVOICE.AMMOUNT) AS NORMAL_AMMOUNT,
0 AS SPECIAL_AMMOUNT,
SUM(I.AMMOUNT) AS TOTAL_AMMOUNT,
SUM(I.POINTS) AS NORMAL_POINTS,
0 AS SPECIAL_POINTS,
SUM(INVOICE.POINTS) AS TOTAL_POINTS,
SUM(EXCHANGE.TOTALPOINTS) AS EXCHANGED_POINTS
FROM CLIENT C,
INVOICE I,
EXCHANGE E
WHERE C.ID = I.IDCLIENT
AND I.IS_SPECIAL_PROMO = FALSE
AND C.ID = E.IDCLIENT
AND C.IDPROMO = 1

Does this give the expected lines for each section? note that they grouping and summing isn't implemented in this bit.

When you are satisfied that you know what you have got and wheather its right or not then let me know anf I'll do the last bit.

TTFN

PCLFW