[SOLVED] Can we HOLD FORMAT SQLPSTGR to a temporary file? (Yes, we can)
As topic.
We tried:
TABLE FILE CAR
PRINT CAR
BY COUNTRY
WHERE RECORDLIMIT EQ 1;
ON TABLE HOLD AS #CAR FORMAT SQLPSTGR
END
That unfortunately throws an error:
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 42601
: (0) [42601] ERROR: syntax error at or near "#"
Position: 15
(FOC1414) EXECUTE IMMEDIATE ERROR.
The ON TABLE HOLD statement does a DROP/CREATE table, so creating the temporary table beforehand doesn't look like a workaround either...
Is there a trick to this? Am I missing something (obvious)?This message has been edited. Last edited by: Wep5622,
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
February 22, 2019, 11:43 AM
BabakNYC
Does it work if you don't have the AS #CAR?
WebFOCUS 8206, Unix, Windows
February 25, 2019, 04:20 AM
Wep5622
No, that creates a permanent table named HOLD in the default schema (public).
But! I found that the solution is actually rather simple; just write the table to the pg_temp schema:
TABLE FILE CAR
PRINT CAR
BY COUNTRY
WHERE RECORDLIMIT EQ 1;
ON TABLE HOLD AS pg_temp.CAR FORMAT SQLPSTGR
END
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :