Indeed.
Thanks for that! I keep getting bitten by that too hehe.
- Naz.
William Garrison wrote:
> 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
>
