I'm having issues with external sql scripts that contain temp tables. It keeps throwing incorrect syntax errors on the temp table. Simply putting CREATE TABLE #Temp ( ID Int ) says incorrect syntax near '#Temp'
Using the MS Sql Server OLE DB driver with a user that has full permissions. Has anyone run into this before? Is there some special indication i'm supposed to make because it is on webfocus?This message has been edited. Last edited by: FP Mod Chuck,
WebFOCUS 8205
July 29, 2019, 12:23 PM
Hallway
Try the SELECT INTO Statement
SELECT *
INTO #newtable
FROM oldtable
WHERE condition;
Hallway
Prod: 8202M1
Test: 8202M4
Repository:
OS:
Outputs:
July 29, 2019, 12:31 PM
reese
Doing that just gives me this error: (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) (FOC1500) : SQL OLE DB Interface: HResult: 0x00000000: (null); No Error Information (FOC1500) : could be obtained from the OLE DB Datasource (FOC1405) SQL PREPARE ERROR.
WebFOCUS 8205
July 29, 2019, 05:07 PM
Waz
How is the SQL script executed ?
Can you post the code ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
July 30, 2019, 08:36 AM
reese
I am using the MS SQL OLE DB Driver and creating a Synonym via Object Type External SQL Scripts. This works just fine for straight SQL select statements, but if there is a temp table in the SQL, it does not run. The code snippet is obviously overly simplified just to show the root issue.
CREATE TABLE #dte (
[ID] VARCHAR(200)
)
INSERT INTO #dte
SELECT DISTINCT
ID
FROM frm_DailyTimeEntry_r11
WHERE Work_Date IS NOT NULL
SELECT
*
FROM #dte
DROP TABLE #dte
WebFOCUS 8205
July 30, 2019, 12:09 PM
Hallway
A ha! You are using this in a synonym. I am unsure on using temp tables in a synonym.
However, what we do is just build a synonym using a straight SQL select statement. Then when using that synonym, if I want to use a temp table, I just use a regular FOCUS request on the synonym and hold it with a format of SAME_DB:
TABLE FILE sqlScriptSynonym
SUM MEASURE1 MEASURE2
BY DIMENSION1
BY DIMENSION2
ON TABLE HOLD AS dte FORMAT SAME_DB PERSISTENCE VOLATILE
END
This will run the SQL script in the synonym on the SQL Server and create a temp table with a B-Tree index on the temp table (the key or index definition is generated from the sort (BY) keys of the TABLE command). WebFOCUS will build a temp masterfile linking to that temptable so you can then do multiple table requests in the same fex on that temp table, and you don't need to include the # in the file name. WebFOCUS does that behind the scenes.
TABLE FILE dte
PRINT *
END
It should be noted that this works on any synonym and creates the hold file on the same DB that the synonym connects to. Be sure to read the docs about the different PERSISTENCE levels.
Got this to work another way by using CTE statements incase anyone comes across this.
Basic POC example:
WITH DIM_CTE
AS
(
SELECT
ID
FROM frm_DailyTimeEntry_r11
),
Item_No_CTE
AS
(
SELECT
ID,
ItemNo
FROM frm_DailyTimeEntry_r11
),
DIM_CTE_Dist
AS
(
SELECT DISTINCT
ID
FROM DIM_CTE
)
SELECT
D.ID,
I.ItemNo
FROM DIM_CTE_Dist AS D
JOIN Item_No_CTE AS I
ON I.ID = D.ID
WebFOCUS 8205
August 12, 2019, 05:00 PM
Hallway
Awesome.
👏
Thanks for sharing.
I've never tried a Common Table Expression in WebFOCUS.
I know that they are really powerful in SQL Server. Especially on Hierarchy Tables.