I am trying to generate a Report from my companies reporting server which is in PostgreSQL. The query runs fine on the PostgreSQL but it is not working when I use this in SQL Editor (Webfocus Developer Studio)

CODE:
SELECT EXTRACT(MONTH FROM txn.timestamp) AS "Month",
EXTRACT(YEAR FROM txn.timestamp) AS "Year",
SUM(txn.amount) AS "Gross Sales Amount",
SUM(txn.quantity) AS "Units Sold",
SUM(txn.revenue) AS "Revenue",
CASE
WHEN EXTRACT(MONTH FROM txn.timestamp) = 1 THEN 'January'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 2 THEN 'February'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 3 THEN 'March'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 4 THEN 'April'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 5 THEN 'May'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 6 THEN 'June'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 7 THEN 'July'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 8 THEN 'August'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 9 THEN 'September'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 10 THEN 'October'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 11 THEN 'November'
WHEN EXTRACT(MONTH FROM txn.timestamp) = 12 THEN 'December'
ELSE 'Others'
END
FROM txn_item txn
JOIN industry i ON txn.industry_id = i.industry_id
WHERE i.industry_id = 12
GROUP BY EXTRACT(YEAR FROM txn.timestamp), EXTRACT(MONTH FROM txn.timestamp), i.industry_id
ORDER BY EXTRACT(YEAR FROM txn.timestamp), EXTRACT(MONTH FROM txn.timestamp);

Errors:

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 00000
: COMMAND FAILED!
L (FOC1405) SQL PREPARE ERROR.
(FOC1517) UNRECOGNIZED COMMAND FROM TXN_ITEM TXN
0 ERROR AT OR NEAR LINE 23 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC376) SYNTAX ERROR OR MISSING ELEMENT IN JOIN/COMBINE COMMAND:
(FOC1517) UNRECOGNIZED COMMAND WHERE I.INDUSTRY_ID = 12 AND EXTRACT(YEAR FROM
TXN.TIMESTAMP) IN (2008,2009,2010,2011,2012)
(FOC1517) UNRECOGNIZED COMMAND GROUP BY EXTRACT(YEAR FROM TXN.TIMESTAMP),
EXTRACT(MONTH FROM TXN.TIMESTAMP), I.INDUSTRY_ID
(FOC1517) UNRECOGNIZED COMMAND ORDER BY EXTRACT(YEAR FROM TXN.TIMESTAMP),
EXTRACT(MONTH FROM TXN.TIMESTAMP);
0 ERROR AT OR NEAR LINE 27 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND


---

Is there a way to use extracts in webfocus. There is a timestamp column and i want to extract them based on months and years.

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

Hi touch, welcome to the forum.

Did you start your SQL with SQLPSTGR ?

There is a process you should follow to use SQL passthrough.

hi waz,

yes i did use it..so my code in the SQL Editor is like

SQL SQLPSTGR PREPARE SQLOUT FOR

-- The Code
-- The Code

END

when i use the first line Webfocus complains that it cannot find a description for SQLOUT


0 ERROR AT OR NEAR LINE 7 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

It clear what happened: The scanning of input for the SQL command (as for TABLE and DEFINE commands) is halted when "END" is encountered on a line by itself.

Look at the error messages. The SQL analyzer never saw the FROM portion of the Select, hence the FOC1400 and FOC1405 messages. And then the WF Agent looked for the next Focus command, and stumbled on the FROM phrase (hence the FOC1517 message: FROM is not a command keyword).

Do something to avoid END on a line by itself -- try parenthesizing:

...
(CASE 
WHEN yada yada
ELSE 'Others' 
END)
...

or simply moving END up to the ELSE line...

...
CASE 
. . .
ELSE 'Others' END
...

or prepend /* */ to END