Focal Point
[SOLVED] Can we HOLD FORMAT SQLPSTGR to a temporary file? (Yes, we can)

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4347031196

February 22, 2019, 11:28 AM
Wep5622
[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 :