CODEWITHSUNDEEP
sqlpostgresqlpentaho
Tim Saylor
Tim Saylor

Reputation: 1054

Can I use a user defined database function in a query in Pentaho Report Designer?

I'm reporting on data from two tables that don't have a sane way to join together. Basically it's inventory in one table, sales in the other, and I'm trying to get the days of inventory on hand by dividing the two. Since I couldn't think of a way to join the tables I abstracted one query into a database function and called it from the other.

Here is the function definition:

CREATE OR REPLACE FUNCTION avgsales(date, text, text, integer) RETURNS numeric
    AS ' SELECT sum(quantity)/(65.0*$4/90.0) as thirty_day_avg
         FROM data_867 JOIN drug_info 
         ON drug_info.dist_ndc = trim(leading ''0'' from data_867.product_ndc)
         WHERE
         rpt_start_dt>= $1-$4 AND
         rpt_end_dt<= $1 AND
         drug_info.drug_name = $2 AND
         wholesaler_name = $3 '
    LANGUAGE SQL;

And here is the report query: 

SELECT
(sum("data_852"."za02")/5)/avgsales(date '2010-11-30', 'Semprex D 100ct', 'McKesson', 30) as doh
FROM
"data_852"
JOIN
"drug_info" ON "drug_info"."dist_ndc" = "data_852"."lin03"
JOIN
"wholesaler_info" ON trim("data_852"."isa06") = trim("wholesaler_info"."isa06")
WHERE
(za01 = 'QA'
OR za01 = 'QP'
OR za01 = 'QI')
and "data_852"."xq02">= DATE '2010-11-30'-5
and "data_852"."xq03"<='2010-11-30'
and drug_info.drug_name = 'Semprex D 100ct'
and wholesaler_info.wholesaler_name = 'McKesson'
;

As it is here, it will run in Pentaho report designer but this is hard coded. When I parameterize the values for the where clause it complains about a syntax error at $1. From looking at the queries that Postgres receives, Pentaho passes the query with it's parameters using $1, $2, etc. I think there might be a conflict with the same variable names being used in our function, or maybe it's just a data type problem.

What could be causing this error? Is it possible to use a function like this in the report query? If not, how can I do something similar using the Report Designer?

Upvotes: 1

Views: 2032

Answers (1)

Gavin
Gavin

Reputation: 6460

It is possible. I am using Postgres 8.4 and RD 3.7

create function ret_p(text) 
returns text 
as 
$$ 
select $1;
$$ language sql immutable;

Report designer query

select * from ret_p(${p_val});

where p_val is the parameter name as defined in RD

Upvotes: 1

Related Questions