Re: Query Assistance

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
>