Focal Point
[CLOSED] External SQL Script - Create temp table

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

July 29, 2019, 12:11 PM
reese
[CLOSED] External SQL Script - Create temp table
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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs: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.

Docs here: Using DBMS Temporary Tables as HOLD Files


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
July 30, 2019, 04:25 PM
Waz
quote:
SAME_DB


Agreed


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

August 12, 2019, 04:55 PM
reese
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.


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs: