Use a variable within a query of a DBLINK

Question

Use a variable within a query of a DBLINK

Navigation

#1 by (0 votes)

0

I would like to ask you the following, is it possible to use a variable to filter the information that comes from a query of a DBLINK ?. Something like this:

SELECT *
FROM (

SELECT * FROM dblink('dbname=db port=5432 host=dbhost user=dbuser password=dbpassword',
        'SELECT column_1, column_2, date_in
        FROM table t
        WHERE t.date_in >= $P{start_date}') as 
            check_table(column_1 integer, column_2 text, date_in date)
    ) as b order by checkout_number

This result I use in a report I publish in JasperSoft, but until now I have not managed to use the parameter $ P {start_date} without generating an error. That's why I thought that maybe I could save that value that they pass to me as a parameter in a variable before I get the data from the internal query, and then use that variable as a filter value.

Is it possible to do something of this style?

Thank you.

postgresql jasper-reports subconsultas jasper

asked by db_ 15.01.2018 в 18:41

source

1 answer

Concatenate Time and minutes in SQL server refresh-on-save package does not work in firefox

score 0 · Answer 1

If that variable is inside postgres (it is a column of an accessible table) you can use quote_literal :

SELECT *
FROM (
    SELECT * FROM dblink('dbname=db port=5432 host=dbhost user=dbuser password=dbpassword',
            'SELECT column_1, column_2, date_in
            FROM table t
            WHERE t.date_in >= '||quote_literal(start_date)) as 
                check_table(column_1 integer, column_2 text, date_in date)
        ) as b order by checkout_number