Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] External SQL Script - Create temp table
 Login/Join
 
reese
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: 6 | Registered: January 07, 2019Report This Post
Hallway
Master
posted Hide Post
Try the SELECT INTO Statement 
  
SELECT *
INTO #newtable
FROM oldtable
WHERE condition;


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
reese
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: 6 | Registered: January 07, 2019Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
reese
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: 6 | Registered: January 07, 2019Report This Post
Hallway
Master
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Waz
Expert
posted Hide Post
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!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
reese
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: 6 | Registered: January 07, 2019Report This Post
Hallway
Master
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders