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.rowan@internode.on.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