Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] External SQL Script - Create temp table
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] External SQL Script - Create temp table
 Login/Join
 
Member
posted
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
 
Posts: 5 | Registered: January 07, 2019Reply With QuoteReport This Post
Guru
posted Hide Post
Try the SELECT INTO Statement
  
SELECT *
INTO #newtable
FROM oldtable
WHERE condition;


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 381 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: January 07, 2019Reply With QuoteReport This Post
Expert
posted Hide Post
How is the SQL script executed ?

Can you post the code ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6074 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: January 07, 2019Reply With QuoteReport This Post
Guru
posted Hide Post
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
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 381 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
SAME_DB


Agreed


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6074 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: January 07, 2019Reply With QuoteReport This Post
Guru
posted Hide Post
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
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 381 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] External SQL Script - Create temp table

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.