We currently have some process where we are taking a file and building a DB2 that is then used for reporting. I was curious if as part of building that file we can create defines/computes/titles on the master file in Web FOCUS.

I know we could create a 2nd master file that points to the DB2 that contians these attributes but I was hoping to avoid having the clutter and confusion of 2 master files for 1 actual file.

Forgive me if this is a silly question of if there is some simpler way to solve this problem, we are usually able to just report straight form our files and are newer to creating these tables that are then used for reporting.

Thanks,

-Luke

This message has been edited. Last edited by: <Emily McAllister>,

Are you building the master ?

What is the process.

I think you could do this and have one master

One benefit of keeping two separate master files is that when you recreate or refresh the metadata your DEFINEs COMPUTEs and TITLEs won't be overwritten. But if everything is in a single master you'll have to keep track of all the additional objects that don't exist in the DB2 table.

quote:

Originally posted by Waz:
Are you building the master ?

What is the process.

I think you could do this and have one master

Waz,

I am building both the Master and the DB2 and below is a fake example of what my code looks like.

 
APP PREPENDPATH webfocus_folder
APP HOLD webfocus_folder
SQL DB2 SET ISOLATION NC

-SET ASNAMES = ON

DEFINE FILE filename
DEFINE1/D1=1;
DEFINE2/D2=2;
END

TABLE FILE filename
SUM
     DEFINE1 AS 'NEW_NAME1'
     DEFINE2 AS 'NEW_NAME2'
     FIELD1 AS 'NEW_NAME3'
     FIELD2 AS 'NEW_NAME4'
BY FIELD3 AS 'NEW_NAME5'
BY FIELD4  AS 'NEW_NAME6'
ON TABLE HOLD AS webfocus_folder/saved_filename FORMAT DB2 TABLENAME server_library/db2_name
END 

This process is doing what I want for the most part, the exception being that I dont know how to create new titles and constant defines/computes. I tried doing it on the master file but unfortunatley that gets created but the issue with that option is that once i run the procedure again it wipes the changes i made when creating the new file. If there is a way to define these elements of the master file within the code i would be happy to add that, i am just not familiar with how to do that or if it can even be done.

Thanks,

-Luke

quote:

Originally posted by BabakNYC:
One benefit of keeping two separate master files is that when you recreate or refresh the metadata your DEFINEs COMPUTEs and TITLEs won't be overwritten. But if everything is in a single master you'll have to keep track of all the additional objects that don't exist in the DB2 table.

is there a way to keep track of all the extra objects? Depending on how that is done it might work better for me to use that process.

If you have an example of how to track those i would appreciate seeing it.

Thanks,

-Luke

Luke:
You can assign TITLE to a DEFINE as follows:
DEFINE FILE CAR
PROFIT/D12.2 TITLE 'Profit' = RCOST -DCOST;
END
TABLE FILE CAR
SUM PROFIT
BY COUNTRY
END

Also, ASNAME is a SET command that sets the environment not a -SET (which is used to populate an &Variable).

SET ASNAME effects the way a field is put in a HOLD file so you should look up the documentation to make sure you're getting the results you expect.

quote:

Originally posted by BabakNYC:
Luke:
You can assign TITLE to a DEFINE as follows:
DEFINE FILE CAR
PROFIT/D12.2 TITLE 'Profit' = RCOST -DCOST;
END
TABLE FILE CAR
SUM PROFIT
BY COUNTRY
END

Also, ASNAME is a SET command that sets the environment not a -SET (which is used to populate an &Variable).

SET ASNAME effects the way a field is put in a HOLD file so you should look up the documentation to make sure you're getting the results you expect.

Thanks for the heads up about the difference between SET and -SET, I will take a look at the documentation.

Also i see how to define the title of a define, is there a way to do that for a Sum and By fields?

Thanks,

-Luke

SUM RCOST AS 'Retail,Cost'

AS xxx should do it, but if you want it in the HOLD file follow the rules of SET ASNAME and SET HOLDATTR

This message has been edited. Last edited by: BabakNYC,

Luke,
Since you create your MASTER dynamically, you have to add your DEFINEs & COMPUTEs dynamically also.
Here is a small example using the app DANNY:

  
APP HOLD DANNY
TABLE FILE CAR
SUM RETAIL_COST DEALER_COST
BY COUNTRY 
BY CAR
ON TABLE HOLD AS SCAR
END
-RUN
FILEDEF SCARMAS DISK DANNY/SCAR.MAS (APPEND
-RUN
-WRITE SCARMAS COMPUTE PROFIT/D6 = RETAIL_COST - DEALER_COST; ,TITLE=Profit, $
-RUN
TABLE FILE SCAR
PRINT RETAIL_COST DEALER_COST COMPUTE PROFIT;
BY COUNTRY
BY CAR
END