PostgreSQL query returns a BigInt instead of Decimal

Classic Servoy
1

Hi everybody,

I’ve made a query to get back a percentage (result is <= 1).
But PostgreSQL returns a BigInt instead of a Decimal type. As a result the value returned is rounded to 0 or 1

How can I force to get a Decimal result?

The query:

SELECT (SELECT COUNT(t9.fld_id)
			FROM tbl_proposals AS t9
			WHERE t9.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
				AND t9.fld_status = 'Won')
		/COUNT(t10.fld_id)
FROM tbl_proposals AS t10
WHERE t10.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
2

Hi Foobrother,

Foobrother:
How can I force to get a Decimal result?

The query:

SELECT (SELECT COUNT(t9.fld_id)

		FROM tbl_proposals AS t9
		WHERE t9.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
			AND t9.fld_status = 'Won')
	/COUNT(t10.fld_id)

FROM tbl_proposals AS t10
WHERE t10.fld_date_entered BETWEEN ‘1999-01-01’ AND ‘2009-12-01’

You can cast the result to a Numeric or Float by adding ::float or ::numeric or more specifically ::numeric(5,2).
So your query will look like this:

SELECT ((SELECT COUNT(t9.fld_id)
			FROM tbl_proposals AS t9
			WHERE t9.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
				AND t9.fld_status = 'Won')
		/COUNT(t10.fld_id))::numeric(5,2)
FROM tbl_proposals AS t10

Hope this helps.

3

ROCLASI:
Hi Foobrother,

You can cast the result to a Numeric or Float by adding ::float or ::numeric or more specifically ::numeric(5,2).
So your query will look like this:

SELECT ((SELECT COUNT(t9.fld_id)

		FROM tbl_proposals AS t9
		WHERE t9.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
			AND t9.fld_status = 'Won')
	/COUNT(t10.fld_id))::numeric(5,2)

FROM tbl_proposals AS t10




Hope this helps.

It works!

Thank you ROCLASI have a good weekend :mrgreen: