Hi,<br />
<br />
i?m facing a rather unpleasant problem...for a client report I?d like to show specific data counts in a Pie chart. I order to accuire the result table I need my idea was to create a custom temp. table in my query that will be filled by SET variables throughout the request. In MySQL Shell and also in the workbench it works like a charm, but Birt simply won?t generate the tables for me...any ideas anyone?<br />
<br />
<strong class='bbc'>Setup:</strong><br />
JDBC Data Source with MySQL Connector Driver<br />
Birt Version 4.2<br />
<br />
<br />
<span style='font-size: 18px;'><strong class='bbc'>Query:</strong></span><br />
<br />
SET
@V1 := (<br />
SELECT COUNT(*)<br />
FROM km_deviceinfo JOIN km_devicestatus ON km_deviceinfo.imei=km_devicestatus.imei <br />
WHERE missing=1 <br />
AND online=0 <br />
AND last_connect IS NOT NULL<br />
AND last_connect != '' <br />
AND com_id IS NOT NULL <br />
AND com_id > '' <br />
AND machine_id IS NOT NULL <br />
AND machine_id > ''<br />
AND DATEDIFF(CURDATE(), missing_detection) > 30<br />
AND DATEDIFF(CURDATE(), missing_detection) < 100<br />
);<br />
<br />
SET
@V2 := (<br />
SELECT COUNT(*)<br />
FROM km_deviceinfo JOIN km_devicestatus ON km_deviceinfo.imei=km_devicestatus.imei <br />
WHERE missing=1 <br />
AND online=0 <br />
AND last_connect IS NOT NULL<br />
AND last_connect > '' <br />
AND com_id IS NOT NULL <br />
AND com_id > '' <br />
AND machine_id IS NOT NULL <br />
AND machine_id > ''<br />
AND DATEDIFF(CURDATE(), missing_detection) > 100<br />
AND DATEDIFF(CURDATE(), missing_detection) < 183<br />
);<br />
<br />
SET
@V3 := (<br />
SELECT COUNT(*)<br />
FROM km_deviceinfo JOIN km_devicestatus ON km_deviceinfo.imei=km_devicestatus.imei <br />
WHERE missing=1 <br />
AND online=0 <br />
AND last_connect IS NOT NULL<br />
AND last_connect > '' <br />
AND com_id IS NOT NULL <br />
AND com_id > '' <br />
AND machine_id IS NOT NULL <br />
AND machine_id > ''<br />
AND DATEDIFF(CURDATE(), missing_detection) > 187<br />
AND DATEDIFF(CURDATE(), missing_detection) < 365<br />
);<br />
<br />
SET
@V4 := (<br />
SELECT COUNT(*)<br />
FROM km_deviceinfo JOIN km_devicestatus ON km_deviceinfo.imei=km_devicestatus.imei <br />
WHERE missing=1 <br />
AND online=0 <br />
AND last_connect IS NOT NULL<br />
AND last_connect > '' <br />
AND com_id IS NOT NULL <br />
AND com_id > '' <br />
AND machine_id IS NOT NULL <br />
AND machine_id > ''<br />
AND DATEDIFF(CURDATE(), missing_detection) > 365<br />
);<br />
<br />
DROP TABLE IF EXISTS lost_count;<br />
<br />
CREATE TEMPORARY TABLE lost_count (<br />
offline_since VARCHAR(50) NOT NULL,<br />
device_count INT(20) NOT NULL<br />
);<br />
<br />
INSERT INTO lost_count (offline_since,device_count) VALUES ("30 days",
@V1); <br />
INSERT INTO lost_count (offline_since,device_count) VALUES ("1 month",
@V2);<br />
INSERT INTO lost_count (offline_since,device_count) VALUES ("3 monts",
@V3);<br />
INSERT INTO lost_count (offline_since,device_count) VALUES ("1 year",
@V4);<br />
<br />
SELECT * FROM lost_count;<br />
<strong class='bbc'><span style='font-size: 18px;'><br />
<br />
Birt Error Message while executing query:</span></strong><br />
<br />
Cannot get the result set metadata.<br />
org.eclipse.birt.report.data.oda.JDBCException: SQL statement does not return a ResultSet object<br />
SQL error #1: You have an error in your SQL Syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'SET<br />
@V2 := (<br />
SELECT COUNT(*)<br />
FROM km_deviceinfo JOIN km_devicestatus ON ' at line 16;<br />
<br />
com.mysql.jdbc.exceptions.MYSQLSyntaxErrorException: You have an error in your SQL Syntax; check the manual <br />
that corresponds to your MySQL server version for the right syntax to use near'SET<br />
@V2 := (<br />
SELECT COUNT(*)<br />
FROM km_deviceinfo JOIN km_devicestatus ON ' at line 16<br />
<br />
Reason:<br />
<br />
A BIRT Exception occured.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Any help would be much appreciated...
