Re: Type mismatch problem

On 06/02/2012 09:52, Michael Rowan wrote:
> As a total beginner who has for decades used an application that would allow type mismatches like boolean*numeric I tried the following select:
>
> SELECT sum(cost*quantity)*(sales_type=1) AS sales_type1, sum(cost*quantity)*(sales_type=2) AS sales_type2 FROM etc etc
>
> In the above, cost and quantity are TYPE numeric(9,2), sales_type is smallint.
>
> PostgreSQL does not allow numeric*boolean. The error message ends with "You might need t" which kinda leaves me hanging.
>
> Any clues would be most welcome.
>
>
> Michael Rowan
> mike(dot)rowan(at)internode(dot)on(dot)net
>
> 11 Kingscote Street
> ALBERTON
> South Australia 5014
>
> tel 618 8240 3993

In PostgreSQL boolean are not numeric as you have found. you can use a
CASE statement to get the desired result.
SELECT
CASE
WHEN sales_type = 1 THEN sum(cost*quantity)
ELSE 0
END as sales_type2
FROM etc etc

Its a little verbose but it works.

Of course you can use the entire case statement as a numeric also as
long as all return types are numeric:
SELECT
CASE
WHEN sales_type = 1 THEN 1
ELSE 0
END * sum(cost*quantity) as sales_type2
FROM etc etc

Hope this helps