ReportServer Forum

bpeikes

Member

Registered: 2016-10-29

Is there any way to use multi statement for a Dynamic list with mysql?

We want to use multi-statement sql with a dynamic list report. For example:

DROP TEMPORARY TABLE IF EXISTS tmpEntIds;

CREATE TEMPORARY TABLE tmpEntIds
(
    entity_id INT NOT NULL PRIMARY KEY
);
INSERT INTO tmpEntIds
SELECT DISTINCT entity_id FROM XXX.YYYY ldp WHERE ldp.end_date IS NULL;

SELECT *
FROM XXX.ZZZ t
WHERE t.entity_id IN (SELECT entity_id FROM tmpEntIds);

I get an error when we try to run this. Is there another way to send multiple statements?

jalbrecht

Administrator

Registered: 2016-10-21

Re: Is there any way to use multi statement for a Dynamic list with mysql?

Dear bpikes,
it is not possible to execute several sql statements within a dynamic list on a Relational Database datasource. I might not fully understand your example but would it not be possible to use a subquery like that:

SELECT *
  FROM
    XXX.ZZZ t
    join (SELECT DISTINCT entity_id FROM XXX.YYYY ldp WHERE ldp.end_date IS NULL) u
      on t.entity_id = u.entity_id

? wbr jan