Focal Point
Writing SQLS over HOLD files...

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

June 03, 2007, 07:23 AM
focuzsambit
Writing SQLS over HOLD files...
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
June 03, 2007, 08:01 AM
FrankDutch
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

June 04, 2007, 10:15 AM
Jason K.
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.
June 04, 2007, 11:17 AM
TexasStingray
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

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
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