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     SQL passthrough and hold files

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SQL passthrough and hold files
 Login/Join
 
thebrettd
Member
posted
Hi all.

I have a report that uses SQL SQLORA passthrough to build a table. I want to save that table, and do some more processing
inside a second SQL SQLORA block.
Here is the basic idea:

SQL SQLORA
select * from someTableName
;
TABLE
ON TABLE HOLD AS EMPS
END

SQL SQLORA
SELECT *
FROM EMPS
WHERE someWhereConditions
TABLE
ON TABLE HOLD AS FINAL
END
-RUN

When I run the report webFOCUS complains
(FOC1400) SQLCODE IS 942 (HEX: 000003AE) (FOC1409) SQL TABLE NOT FOUND. (FOR TABLE: CHECK SEGNAME AND TABLE) : ORA-00942: table or view does not exist : Erroneous token: EMPS

So it seems like my first table isnt being saved. Any idea what im doing wrong here?


Dev: WF 5.2.1
Production: WF 5.2.1
Testing: WF 7.1.3
 
Posts: 26 | Registered: October 30, 2006Report This Post
hammo1j
Master
posted Hide Post
ON TABLE HOLD AS EMPS FORMAT SQLORA


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
thebrettd
Member
posted Hide Post
Thank you!


Dev: WF 5.2.1
Production: WF 5.2.1
Testing: WF 7.1.3
 
Posts: 26 | Registered: October 30, 2006Report This Post
thebrettd
Member
posted Hide Post
Ok that worked great to solve my first problem but now webFOCUS is complaining again:

(FOC1421) TABLE EXISTS ALREADY. DROP IT OR USE ANOTHER TABLENAME : ORA-00955: name is already used by an existing object : Erroneous token: EMPS

I assure you that EMPS is used nowhere in the report except to reference the hold file I just created.

Arnt these hold files supposed to be deleted when the connection to the server is terminated? If so I don't understand what this error message could mean.
I do not see anything in my webFOCUS manual's glossary about DROP either..


Dev: WF 5.2.1
Production: WF 5.2.1
Testing: WF 7.1.3
 
Posts: 26 | Registered: October 30, 2006Report This Post
Tony A
Expert
posted Hide Post
Use SQL passthru to achieve this -

SQL SQLORA
DROP TABLE EMPS;
END
-RUN

Should do it for you.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
thebrettd
Member
posted Hide Post
You guys rock.

But to bump my question: Arent these hold files supposed to be temporary anyway? Or is it because I used the oracle SQL passthrough that i need to issue the drop command?


Dev: WF 5.2.1
Production: WF 5.2.1
Testing: WF 7.1.3
 
Posts: 26 | Registered: October 30, 2006Report This Post
Tony A
Expert
posted Hide Post
Any file written to the EDATEMP file is temporary (unless TEMPERASE is set to OFF), anywhere else and you need to do the cleaning for yourself - this includes all SQL data sources.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Michael Simon
Member
posted Hide Post
How do you run the same report same time using this method without running into problems?


Have used WF 5.x, 7.x w/RC, BID, MR
WAS 5.x/6.x
AIX 5.3.0
 
Posts: 29 | Location: Wine Country | Registered: October 10, 2006Report This Post
GinnyJakes
Expert
posted Hide Post
quote:
SQL SQLORA
select * from someTableName
;
TABLE
ON TABLE HOLD AS EMPS
END

SQL
SELECT *
FROM EMPS
WHERE someWhereConditions
TABLE
ON TABLE HOLD AS FINAL
END
-RUN

If you want to use the temporary storage option of WebFOCUS, use your original syntax to create EMPS. That will put it in EDATEMP. Then I took the SQLORA off of the second select. It will know to get the file from EDATEMP. Or you can use a TABLE request to do the second query if you wish. Then you don't have to do any cleaning up.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report 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     SQL passthrough and hold files

Copyright © 1996-2020 Information Builders