Page 1 of 1
Use parameters in Query Text of Data Source
Posted: Fri May 13, 2016 1:49 pm
by programmer2
I need some help with using parameters inside Query Text of Data Source. I have tried to pass a date range from user to sql query like 'select @DB from dual' but when report rendering always I get an error Load Data error, and in the report checker i get Parse error: The name 'DB' doesn't exist in current context.
Thank you in advance
Re: Use parameters in Query Text of Data Source
Posted: Mon May 16, 2016 6:15 am
by Alex K.
Hello,
Can you please send us your report with test data which reproduces the issue for analysis.
Thank you.
Re: Use parameters in Query Text of Data Source
Posted: Mon May 16, 2016 8:28 am
by programmer2
Yes, of course I uploaded a test report with a signle simple select from dual sql.
Thank you
Re: Use parameters in Query Text of Data Source
Posted: Mon May 16, 2016 12:10 pm
by Alex K.
Hello,
Oracle SQL parameters are specified using ':'. Please try to use the following query:
Thank you.
Re: Use parameters in Query Text of Data Source
Posted: Mon May 16, 2016 1:36 pm
by programmer2
Trying to make it work but I'm missing something. When trying with the declaration of parameter in screenshot I'm still getting the same error: Retrieve columns error.
- Capture.PNG (21.15 KiB) Viewed 5044 times
Thank you
Re: Use parameters in Query Text of Data Source
Posted: Tue May 17, 2016 7:01 am
by HighAley
Hello.
There should not be curly braces in the Expression.
Please, remove them.
Thank you.
Re: Use parameters in Query Text of Data Source
Posted: Mon Jun 06, 2016 2:30 am
by xiaochris
Hi ,
I'm also facing the same issue. I'm unable to run the following query. I'm using MySQL connection.
Code: Select all
SET @tables = (
SELECT GROUP_CONCAT(DISTINCT CONCAT(TABLE_SCHEMA, ".", TABLE_NAME) SEPARATOR ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'peripherals'
AND TABLE_SCHEMA LIKE 'tracker'
);
SET @select = (
SELECT
GROUP_CONCAT(
DISTINCT CONCAT(TABLE_SCHEMA, ".", TABLE_NAME, ".`peripherals` AS '", TABLE_SCHEMA, ".", TABLE_NAME, ".peripherals'")
SEPARATOR ', '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'peripherals'
AND TABLE_SCHEMA LIKE 'tracker'
);
SET @x := CONCAT('SELECT ', @select, ' FROM ', @tables);
PREPARE stmt FROM @x;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Re: Use parameters in Query Text of Data Source
Posted: Mon Jun 06, 2016 12:44 pm
by Alex K.
Hello,
Can you please send us a test data for your query. Or send us a simple query which reproduces the issue for analysis.
Thank you.