SQL does not substract

[hmoner]

Hi guys
Lemme explain the situation in 2 lines:
DB Structure:
http://img133.echo.cx/img133/6610/dbstructure6hs.jpg

Client goes to computer shop. Shop has 1 or more NORMAL promotions (of the kind: for each u$sXX ammount you buy, you get X qtty of points to exchange them later by gifts). EACH NORMAL PROMOTION can have 0 or more special "sub"promotions (same kind as normal promotion, but you get more points)
For each purchase, client gets an invoice with ammount, points he got, and details about if promotion is normal or special (or both)
Later, client can exchange points by gifts...

... there's more but I think that this will be enought to explain ...

My problem is:
I was requested to report this (for all clients of actual promotion):

NAME, ACTUAL_POINTS, NORMAL_AMMOUNT, SPECIAL_AMMOUNT, TOTAL_AMMOUNT, NORMAL_POINTS, SPECIAL_POINTS, TOTAL_POINTS, EXCHANGED_POINTS
(order can vary)

*******************************
But, following SQL gives me ACTUAL_POINTS = 0 for all records...
It should give
"SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS)"
for each record (client)
*******************************

Please look image so you understand the error i get ...
http://img61.echo.cx/img61/8879/dbqueryproblem5it.jpg

--------------------------------------

SELECT
CLIENT.NAME AS CLIENT,
SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS) AS ACTUAL_POINTS,
SUM(CASE WHEN IS_SPECIAL_PROMO = TRUE THEN 0 ELSE INVOICE.AMMOUNT END) AS NORMAL_AMMOUNT,
SUM(CASE WHEN IS_SPECIAL_PROMO = TRUE THEN INVOICE.AMMOUNT ELSE 0 END) AS SPECIAL_AMMOUNT,
SUM(INVOICE.AMMOUNT) AS TOTAL_AMMOUNT,
SUM(CASE WHEN IS_SPECIAL_PROMO = TRUE THEN 0 ELSE INVOICE.POINTS END) AS NORMAL_POINTS,
SUM(CASE WHEN IS_SPECIAL_PROMO = TRUE THEN INVOICE.POINTS ELSE 0 END) AS SPECIAL_POINTS,
SUM(INVOICE.POINTS) AS TOTAL_POINTS,
SUM(EXCHANGE.TOTALPOINTS) AS EXCHANGED_POINTS
FROM
CLIENT
LEFT OUTER JOIN INVOICE ON (CLIENT.ID = INVOICE.IDCLIENT)
LEFT OUTER JOIN EXCHANGE ON (CLIENT.ID = EXCHANGE.IDCLIENT)
WHERE
(CLIENT.IDPROMO = 1) // I replace "1" by :PARAM_ID_NORMALPROMOTION
GROUP BY
CLIENT.NAME,ACTUAL_POINTS
ORDER BY
CLIENTE.NAME

--------------------------------------

I tried replacing "SUM(INVOICE.POINTS)" by 10000 to test if substraction worked, but i got 10000 for every record, and I also replaced "- SUM(EXCHANGE.TOTALPOINTS)" by 1000 but i got -1000 for every record, so it seems that both fields are taken as NULL...

Does any1 have a solution to correct this ?

Does any1 have another way to make this query ? Easily ?

thanks 4 reading and helping :rolleyes:

[pclfw]

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.

[hmoner]

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:

[pclfw]

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