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 (
) 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,
Try the SELECT INTO Statement
SELECT * INTO #newtable FROM oldtable WHERE condition;
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.
How is the SQL script executed ?
Can you post the code ?
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
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.
Docs here: Using DBMS Temporary Tables as HOLD Files
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
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.
|Powered by Social Strata|