June 03, 2007, 07:23 AM
focuzsambitWriting 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
June 03, 2007, 08:01 AM
FrankDutchSam
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.
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.
June 04, 2007, 11:17 AM
TexasStingrayFocuzsambit, #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
June 04, 2007, 04:16 PM
MickeyHere 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
June 05, 2007, 07:33 AM
LucasHere 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