Query Assistance
Is anyone able to tell me why in the last column of the returned result set, the value calculated is always 0?
QUERY:
SELECT products.productid,
products.cost,
products.srp,
CASE WHEN products.srp > 0 THEN (products.srp - products.cost) * 100 / products.srp ELSE 0 END AS margin,
products.type,
products.gstexempt,
productpointvalues.earnvalue,
productpointvalues.redeemvalue,
productpointvalues.earnvalue / productpointvalues.redeemvalue AS redemptionmargin
FROM categories, products
LEFT OUTER JOIN productpointvalues USING (productid)
WHERE products.active IS TRUE
AND products.catid = categories.catid
AND products.catid = 2
ORDER BY products.name;
RESULT SET:
productid | cost | srp | margin | type | gstexempt | earnvalue | redeemvalue | redemptionmargin
-----------+-------+--------+----------------------+------+-----------+-----------+-------------+------------------
716 | 8.60 | 10.00 | 14.0000000000000000 | N | f | 50 | 1500 | 0
15 | 87.00 | 100.00 | 13.0000000000000000 | N | f | 500 | 10000 | 0
13 | 26.10 | 30.00 | 13.0000000000000000 | N | f | 150 | 3000 | 0
1189 | 0.00 | 40.00 | 100.0000000000000000 | N | f | 200 | 4000 | 0
14 | 43.50 | 50.00 | 13.0000000000000000 | N | f | 250 | 5000 | 0
Indeed.
Thanks for that! I keep getting bitten by that too hehe.
- Naz.
William Garrison wrote:
Show quoted text
My guess is that integer division is to blame: 50 divided by 1500 =
0.03 which rounds to zero. You probably have to cast them to real
before doing the division.Naz Gassiep wrote:
Is anyone able to tell me why in the last column of the returned
result set, the value calculated is always 0?QUERY:
SELECT products.productid,
products.cost,
products.srp,
CASE WHEN products.srp > 0 THEN (products.srp -
products.cost) * 100 / products.srp ELSE 0 END AS margin,
products.type,
products.gstexempt,
productpointvalues.earnvalue,
productpointvalues.redeemvalue,
productpointvalues.earnvalue /
productpointvalues.redeemvalue AS redemptionmargin
FROM categories, products
LEFT OUTER JOIN productpointvalues USING (productid)
WHERE products.active IS TRUE
AND products.catid = categories.catid
AND products.catid = 2
ORDER BY products.name;RESULT SET:
productid | cost | srp | margin | type | gstexempt
| earnvalue | redeemvalue | redemptionmargin
-----------+-------+--------+----------------------+------+-----------+-----------+-------------+------------------716 | 8.60 | 10.00 | 14.0000000000000000 | N | f
| 50 | 1500 | 0
15 | 87.00 | 100.00 | 13.0000000000000000 | N | f
| 500 | 10000 | 0
13 | 26.10 | 30.00 | 13.0000000000000000 | N | f
| 150 | 3000 | 0
1189 | 0.00 | 40.00 | 100.0000000000000000 | N | f
| 200 | 4000 | 0
14 | 43.50 | 50.00 | 13.0000000000000000 | N | f
| 250 | 5000 | 0---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Import Notes
Reply to msg id not found: 45FA3B15.9000609@mobydisk.com
My guess is that integer division is to blame: 50 divided by 1500 = 0.03
which rounds to zero. You probably have to cast them to real before
doing the division.
Naz Gassiep wrote:
Show quoted text
Is anyone able to tell me why in the last column of the returned result
set, the value calculated is always 0?QUERY:
SELECT products.productid,
products.cost,
products.srp,
CASE WHEN products.srp > 0 THEN (products.srp -
products.cost) * 100 / products.srp ELSE 0 END AS margin,
products.type,
products.gstexempt,
productpointvalues.earnvalue,
productpointvalues.redeemvalue,
productpointvalues.earnvalue /
productpointvalues.redeemvalue AS redemptionmargin
FROM categories, products
LEFT OUTER JOIN productpointvalues USING (productid)
WHERE products.active IS TRUE
AND products.catid = categories.catid
AND products.catid = 2
ORDER BY products.name;RESULT SET:
productid | cost | srp | margin | type | gstexempt |
earnvalue | redeemvalue | redemptionmargin
-----------+-------+--------+----------------------+------+-----------+-----------+-------------+------------------716 | 8.60 | 10.00 | 14.0000000000000000 | N | f
| 50 | 1500 | 0
15 | 87.00 | 100.00 | 13.0000000000000000 | N | f
| 500 | 10000 | 0
13 | 26.10 | 30.00 | 13.0000000000000000 | N | f
| 150 | 3000 | 0
1189 | 0.00 | 40.00 | 100.0000000000000000 | N | f
| 200 | 4000 | 0
14 | 43.50 | 50.00 | 13.0000000000000000 | N | f
| 250 | 5000 | 0---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match