using a function in where

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

using a function in where

Glenn Schultz
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;

Reply | Threaded
Open this post in threaded view
|

Re: using a function in where

Andrew Gierth
>>>>> "Glenn" == Glenn Schultz <[hidden email]> 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)

Reply | Threaded
Open this post in threaded view
|

Re: using a function in where

Alban Hertroys-4
In reply to this post by Glenn Schultz

> On 3 Dec 2018, at 8:06, Glenn Schultz <[hidden email]> 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.