Hi guys,

So there is another problem which I am stuck at. I have an SQL query (in mySQL) that works fine (the results show in the HTML web browser when testing) But when I go ahead to make the Report using SQLOUT as the table data source, it throws the error "Error Parsing MASTER File SQLOUT (0)"

My query is:

SELECT COUNT(t.business_id) as "Number of Merchants",
EXTRACT(MONTH FROM t.local_timestamp) AS "Month",
CASE
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 1 THEN 'January'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 2 THEN 'February'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 3 THEN 'March'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 4 THEN 'April'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 5 THEN 'May'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 6 THEN 'June'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 7 THEN 'July'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 8 THEN 'August'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 9 THEN 'September'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 10 THEN 'October'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 11 THEN 'November'
WHEN EXTRACT(MONTH FROM t.local_timestamp) = 12 THEN 'December'
ELSE 'Others' END,
EXTRACT(YEAR FROM t.local_timestamp),
SUM(ti.amount) as "Amount",
SUM(ti.item) as "Items Transacted"
FROM txn t JOIN txn_item ti ON t.txn_id = ti.txn_id
JOIN product p ON p.product_id = ti.product_id
WHERE p.processing_type_id not IN (36) and t.service_id = 1 and EXTRACT(YEAR FROM t.local_timestamp) = '2012'
GROUP by EXTRACT(MONTH FROM t.local_timestamp);

I have tried doing some research and it seems like Webfocus does not understand some of the code when it is preparing the SQLOUT. Could it be the EXTRACT or CASE code? As I think that is does not understand this

This message has been edited. Last edited by: Kerry,

I have managed to make the code slightly efficient but it still gives me this error

SELECT DATE_FORMAT(t.local_timestamp,"%Y") as "Year",
DATE_FORMAT(t.local_timestamp,"%M") as "Month",
COUNT(t.business_id) as "Number of Merchants",
SUM(ti.amount) as "Amount",
SUM(ti.item) as "Items Transacted"
FROM txn t JOIN txn_item ti ON t.txn_id = ti.txn_id
JOIN product p ON p.product_id = ti.product_id
WHERE p.processing_type_id not IN (36) and t.service_id = 1 and DATE_FORMAT(t.local_timestamp,"%Y") = '2012'
GROUP by DATE_FORMAT(t.local_timestamp,"%M");

nevermind it seems to be resolved...seems to be like there is some issue with the database and not the query hence the results were failing.

If you're trying to create a HOLD file, perhaps this AS statement generates a funny column name in the Master...

quote:

"Number of Merchants"