[CLOSED] Creating a temporary master file on the fly
I have a case where I create a dynamic Oracle table through a fex and the table name is dynamic to eliminate contention between users. The table name includes the project the user is working on the users name and a date time stamp.
The table needs to populated at the end of my fex and I am trying to do that through a modify statement. The issue is that I need a master file to have the same name as my Oracle table or the insert doesn’t work.
I have done the following and I am not getting an error but now rows are being updated in my table.
SQL SQLORA PREPARE SQLOUT FOR SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'BWL%'||'&PROJECT'||'%'||'&MOD_BY4'||'%'; END
-*
FILEDEF TAB_NM_VAR DISK TAB_NM_VAR.FTM -* TABLE FILE SQLOUT PRINT COMPUTE TABLE_NM/A30=TABLE_NAME; ON TABLE SET HOLDLIST PRINTONLY ON TABLE HOLD AS TAB_NM_VAR FORMAT ALPHA END -RUN
-READ TAB_NM_VAR &TABLE_NM.A30.
-SET &TABLE_NM=TRUNCATE(&TABLE_NM); -*-*
SQL SQLORA PREPARE SQLOUT FOR SELECT PROJ_ID, BUD_RVSN_ID, ORG_ID, ACCT_ID, TOT_BUD_HRS, TOT_BUD_AMT, MODIFIED_BY FROM &TABLE_NM END
APP ENABLE APP HOLD BUDGET_UPDATES -RUN DYNAM ALLOC FILE &TABLE_NM DS 'WEBFOCUS.APP.BASEAPP.&TABLE_NM.FOCUS' NEW REUSE DYNAM ALLOC DD HOLDMAST DA 'WEBFOCUS.APP.BASEAPP.MASTER.DATA' SHR REUSE -RUN TABLE FILE SQLOUT PRINT PROJ_ID BUD_RVSN_ID ORG_ID ACCT_ID TOT_BUD_HRS TOT_BUD_AMT MODIFIED_BY ON TABLE HOLD AS &TABLE_NM FORMAT FOCUS END -RUN APP HOLD
SteveThis message has been edited. Last edited by: <Kathryn Henning>,
November 13, 2013, 03:28 PM
susannah
APP HOLD XSUSANTEST TABLE FILE IBISAMP/CAR SUM DCOST BY COUNTRY BY CAR ON TABLE HOLD AS CARDATA FORMAT FOCUS END -RUN CMD DIR C:\IBI\APPS\XSUSANTEST\CARDATA.* gives me this: 0 NUMBER OF RECORDS IN TABLE= 18 LINES= 10 Volume in drive C has no label. Volume Serial Number is E4E2-4234 Directory of C:\IBI\APPS\XSUSANTEST 11/13/2013 03:25 PM 4,096 cardata.foc 11/13/2013 03:25 PM 214 cardata.mas 2 File(s) 4,310 bytes 0 Dir(s) 46,073,942,016 bytes free
and the master is right there. The APP HOLD dirname does all the work, holding both data and master, unless you say otherwise ; i don't know from your [absent] signature what op sys you're on, but its not windows; i vaguely remember DYNAM ALLOCs from a distant former life.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
November 14, 2013, 11:15 AM
Fernando
Try something like this:
FI MYMAST DISK mymast.mas
-WRITE MYMAST FILE=PMYMAST, SUFFIX=FIX, SEGNAME=MYMAST, SEGTYPE=S0
-WRITE MYMAST FIELD=PROJ_ID,ALIAS=E01,ACTUAL=A05,USAGE=A05, $
-WRITE THEMFD FIELD=BUD_RVSN_ID,ALIAS=E02,ACTUAL=A75,USAGE=A75, $
-RUN
etc...