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
>