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     Writing SQLS over HOLD files...

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Writing SQLS over HOLD files...
 Login/Join
 
focuzsambit
Platinum Member
posted
Hi All

i am working on reporting from teradata datasource.
I need my fex file to be built in such a fashion that there is no volatile table(temporary table of teradata is called volatile table).
So my q/s is
1. Can we write queries over hold files ? i mean i have 10 sets of sqls. So idea is my base sql will pull data from teradata datasource and store in a hold file. Rest of the sqls will be fired over that hold file and result will be stored in another hold file.
2. How to identify the columns of the Hold File ?

any pointers !!

tks,
sam


WF Server: 7.1.4 on Z/OS and Linux, ReportCaster
Data: DB2, DB2/UDB, Adabas, SQL Server, Oracle Output: HTML,PDF,Excel2K
WF Client: Servlet, CGI
 
Posts: 133 | Registered: May 12, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
Sam

You can write focus language (fex) on hold files but not SQL queries

Your first sql on the teradata creates the hold file, if you have 10 sql queries you can append the output in one hold file (if the record layout is the same).

You can open the hold file via the GUI and you will see the available fields.
You can do the same things as you can when creating a report on any other database.

Is this what you wanted to know??

BTW upgrade you signature so we all know what version and systems you are using.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Jason K.
Master
posted Hide Post
if you have the same number of columns in your SQL then you can use a UNION or UNION ALL sql statement to create a single result set.

This would allow one pass to your database and your optimizer may be able to provide a better query plan using this method.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
TexasStingray
Master
posted Hide Post
Focuzsambit, #2 I'm not sure if this is what you want.
TABLE FILE CAR
PRINT 
COUNTRY
CAR
MODEL
ON TABLE HOLD AS MYHOLD1
END
? HOLD MYHOLD1 HOLD
? HOLD 
TABLE FILE HOLD
PRINT *
END


Hope this helps




Scott

 
Posts: 865 | Registered: May 24, 2004Report This Post
Mickey
Silver Member
posted Hide Post
Here is an example of SQL against HOLD files.

quote:

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS T1
END
-RUN
SQL
SELECT *
FROM T1
WHERE COUNTRY = 'ENGLAND';
TABLE
ON TABLE HOLD AS T2
END
-RUN
TABLE FILE T2
PRINT *
END
-RUN
-EXIT


M
 
Posts: 33 | Location: New York, USA | Registered: August 11, 2003Report This Post
Lucas
Silver Member
posted Hide Post
Here is a sample of SQL results being held and then passed to Focus....

SQL DB2
select a.item_no, sum(a.item_qty), b.loc,
 b.date_bld_rate from
prod.reqmnts as a
inner join 
(select veh_ser_no, date_bld_rate, loc, sv_type_cd from 
prod.sched 
 where loc = '017'
 and date_bld_rate = '2007-06-15'
 and sv_type_cd =
'FCB') as b on b.veh_ser_no = a.veh_ser_no
group by a.item_no, b.loc,
 b.date_bld_rate 
with ur;
TABLE
ON TABLE HOLD AS SQLRESULT
END
TABLE FILE SQLRESULT
PRINT *
ON TABLE PCHOLD FORMAT HTML
END


7.6.6 Mainframe
7.6.4 Web Focus
Windows

 
Posts: 45 | Location: Gaffney SC | Registered: March 30, 2007Report 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     Writing SQLS over HOLD files...

Copyright © 1996-2020 Information Builders