using a function in where
All,
I am using the function below to convert a continuous variable to a binned
value. Sometimes a value other than zero is passed through the query. For
example -.5 result value is passed to the query result. The basic of the
query is below.
select
incentivebin(wac, rate, .25)
from
my_table
where incentivebin(was, rate, .25) = 0
I have checked the function works correctly and the raw values match those
values expected from simple subtraction and are in the correct bin. I am
not sure why some values would be allowed through the query. Any ideas
would be appreciated.
Best,
Glenn
CREATE or REPLACE FUNCTION embs_owner.IncentiveBin(IN "Gwac" double
precision,
IN "MtgRate" double precision,
IN "BinSize" double precision)
RETURNS double precision
LANGUAGE 'sql'
PARALLEL SAFE
AS 'select ceiling(($1 - $2)/$3) *$3';
ALTER FUNCTION embs_owner.IncentiveBin(double precision, double precision,
double precision)
OWNER TO embs_owner;
"Glenn" == Glenn Schultz <glenn@bondlab.io> writes:
Glenn> All,
Glenn> I am using the function below to convert a continuous variable
Glenn> to a binned value. Sometimes a value other than zero is passed
Glenn> through the query. For example -.5 result value is passed to the
Glenn> query result. The basic of the query is below.
Glenn> select
Glenn> incentivebin(wac, rate, .25)
Glenn> from
Glenn> my_table
Glenn> where incentivebin(was, rate, .25) = 0
Those two calls to incentivebin() have different parameters - is that an
error in the original query, or a typo in transcribing it to email?
--
Andrew (irc:RhodiumToad)
On 3 Dec 2018, at 8:06, Glenn Schultz <glenn@bondlab.io> wrote:
All,
I am using the function below to convert a continuous variable to a binned value. Sometimes a value other than zero is passed through the query. For example -.5 result value is passed to the query result. The basic of the query is below.select
incentivebin(wac, rate, .25)
from
my_table
where incentivebin(was, rate, .25) = 0
CREATE or REPLACE FUNCTION embs_owner.IncentiveBin(IN "Gwac" double precision,
AS 'select ceiling(($1 - $2)/$3) *$3';
If the difference of (wac - rate) = -0.5, and you multiply that by 4 (divide by .25), ceil will round that correctly to -2. If you then divide by 4 again, you get -0.5 back (which is twice your bin size).
The result matches your function definition with the given parameters.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.