Use parameters in Query Text of Data Source

[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

[Alex K.]

Hello,

Can you please send us your report with test data which reproduces the issue for analysis.

Thank you.

[programmer2]

Yes, of course I uploaded a test report with a signle simple select from dual sql.

DATERANGE.mrt

(4.56 KiB) Downloaded 437 times

Thank you

[Alex K.]

Hello,

Oracle SQL parameters are specified using ':'. Please try to use the following query:

Code: Select all

select :DB from dual

Thank you.

[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 5060 times

Thank you

[HighAley]

Hello.

There should not be curly braces in the Expression.
Please, remove them.

Thank you.

[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;

[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.