Help with SQL - I get column with 0 for each record
Help with SQL - I get column with 0 for each record
(OP)
Hi guys
1º lemme explain situation:
there're clients who buy at a shop. there're promotions in shop (can be normal or special). one promotion says: "for each u$s 100 you buy, you get 1 point". client can exchange points by gifts.
2º Take a look at this image
http://img24.echo.cx/img24/2770/errorsql19dy.jpg
some terms translated:
CLIENTE.NOMBRE = CLIENT.NAME
FACTURACION.PUNTOS = PURCHASE.POINTSOBTAINED
FACTURACION.MONTO = PURCHASE.AMMOUNTOFMONEY
CANJE.TOTALPUNTOS = EXCHANGE.TOTALPOINTS
espromoesp = ISPROMOTIONESPECIAL
PUNTOS_RESTANTES = TOTALPOINTS_LEFT
PUNTOS_CANJEADOS = POINTS_EXCHANGED
CLIENTE.IDPROMO = CLIENTE.IDPROMOTION
**************************************************
**************************************************
does any1 know why i get 0 in PUNTOS_RESTANTES ?
I should get this difference:
PURCHASE.POINTSOBTAINED - EXCHANGE.TOTALPOINTS
I mean, the points client got in all purchases made
minus all the points he exchanged
**************************************************
**************************************************
If there's yet something you don't understand (it is in spanish) please tell me...
THANKS A LOT FOR YOUR HELP
1º lemme explain situation:
there're clients who buy at a shop. there're promotions in shop (can be normal or special). one promotion says: "for each u$s 100 you buy, you get 1 point". client can exchange points by gifts.
2º Take a look at this image
http://img24.echo.cx/img24/2770/errorsql19dy.jpg
some terms translated:
CLIENTE.NOMBRE = CLIENT.NAME
FACTURACION.PUNTOS = PURCHASE.POINTSOBTAINED
FACTURACION.MONTO = PURCHASE.AMMOUNTOFMONEY
CANJE.TOTALPUNTOS = EXCHANGE.TOTALPOINTS
espromoesp = ISPROMOTIONESPECIAL
PUNTOS_RESTANTES = TOTALPOINTS_LEFT
PUNTOS_CANJEADOS = POINTS_EXCHANGED
CLIENTE.IDPROMO = CLIENTE.IDPROMOTION
**************************************************
**************************************************
does any1 know why i get 0 in PUNTOS_RESTANTES ?
I should get this difference:
PURCHASE.POINTSOBTAINED - EXCHANGE.TOTALPOINTS
I mean, the points client got in all purchases made
minus all the points he exchanged
**************************************************
**************************************************
If there's yet something you don't understand (it is in spanish) please tell me...
THANKS A LOT FOR YOUR HELP





RE: Help with SQL - I get column with 0 for each record
GROUP BY 1,2
RE: Help with SQL - I get column with 0 for each record
I get syntax error...
Anyway, I'm getting same result if I change the order of "appearance" in the group by clause...
any ideas ? thanks a lot 4 helping..
the SQL engine I use is www.nexusdb.com and it is compatible with ISO SQL standards, etc.
RE: Help with SQL - I get column with 0 for each record
RE: Help with SQL - I get column with 0 for each record
PUNTOS_RESTANTES is not a field in my DB, this is a field in the query...
"... SUM(FACTURACION.PUNTOS) - SUM(CANJE.TOTALPUNTOS) AS PUNTOS_RESTANTES, ..."
I had to put PUNTOS_RESTANTES in group by clause because i get error if i do not do so... :(
Note:
I also replaced SUM(FACTURACION.PUNTOS) by 10000 to see if the query was substracting, but instead 0 i got 10000 for each record...
After this, i replaced SUM(CANJE.TOTALPUNTOS) by 1000 and i got -1000 in each record...
So, it seems nexus is not taking this values... none of them....
I'm becoming nuts with this... Thanks a lot 4 helping !!
RE: Help with SQL - I get column with 0 for each record
At least this is what is said in the SQL syntax language for Firebird 1.5 which is pretty close to the ANSI92 standard.