How to: |
When used in a request against a relational data source, the HOLD FORMAT SQL_SCRIPT command generates the SQL SELECT statement needed to execute the current query and stores it in the application folder as a file with a .sql extension along with the Master and Access File pair that describes the SQL answer set.
When used in a request against any other type of data source, the HOLD FORMAT SQL_SCRIPT command executes the current query and stores the retrieved values in the application folder as a sequential file with a .ftm extension along with the Master File that describes the retrieved data.
You can use the output from HOLD FORMAT SQL_SCRIPT as the target file for the DB_INFILE function. For information about the DB_INFILE function, see the Using Functions manual.
Note: Once you have the .sql file and its accompanying Master File, you can customize the .sql file using global Dialogue Manager variables. You must declare these global variables in the Master File. For information about parameterizing Master Files with global variables, see the Describing Data With WebFOCUS Language manual.
ON TABLE HOLD AS script_name FORMAT SQL_SCRIPT
where:
Is the name of the .sql file or the .ftm file created as a result of the HOLD FORMAT SQL_SCRIPT command.
The following request against the WF_RETAIL relational data source creates an SQL Script file in the baseapp application:
APP HOLD baseapp TABLE FILE wf_retail SUM REGION STATECODE BY REGION NOPRINT BY STATECODE NOPRINT WHERE REGION EQ 'Central' OR 'NorthEast' WHERE STATECODE EQ 'AR' OR 'IA' OR 'KS' OR 'KY' OR 'WY' OR 'CT' OR 'MA' OR 'NJ' OR 'NY' OR 'RI' ON TABLE HOLD AS RETAIL_SCRIPT FORMAT SQL_SCRIPT END
WF_RETAIL is a sample data source you can create by right-clicking an application on the Reporting Server Web Console and selecting New and then Samples from the context menu.
The result of this request is a script file named retail_script.sql and a corresponding Master and Access File.
The retail_script.sql file contains the following SQL SELECT statement:
SELECT MAX(T11."REGION") AS E01, MAX(T11."STATECODE") AS E02 FROM wrd_dim_geography T11 WHERE (T11."STATECODE" IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT', 'MA', 'NJ', 'NY', 'RI')) AND (T11."REGION" IN('Central', 'NorthEast')) GROUP BY T11."REGION", T11."STATECODE"
The retail_script.mas Master File follows:
FILENAME=RETAIL_SCRIPT, SUFFIX=DB2 , $ SEGMENT=RETAIL_SCRIPT, SEGTYPE=S0, $ FIELDNAME=REGION, ALIAS=E01, USAGE=A15V, ACTUAL=A15V, MISSING=ON, $ FIELDNAME=STATECODE, ALIAS=E02, USAGE=A2, ACTUAL=A2, MISSING=ON, $
The retail_script.acx Access File follows:
SEGNAME=RETAIL_SCRIPT, CONNECTION=CON1, DATASET=RETAIL_SCRIPT.SQL, $
The following request against the EMPLOYEE data source creates a sequential file containing the values retrieved by the request along with a corresponding Master File:
APP HOLD baseapp TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME WHERE DEPARTMENT EQ 'MIS' ON TABLE HOLD AS EMPVALUES FORMAT SQL_SCRIPT END
The sequential file empvalues.ftm contains the following data:
SMITH MARY JONES DIANE MCCOY JOHN BLACKWOOD ROSEMARIE GREENSPAN MARY CROSS BARBARA
The empvalues.mas Master File follows:
FILENAME=EMPVALUES, SUFFIX=FIX , IOTYPE=BINARY, $ SEGMENT=EMPVALUE, SEGTYPE=S0, $ FIELDNAME=LAST_NAME, ALIAS=E01, USAGE=A15, ACTUAL=A16, $ FIELDNAME=FIRST_NAME, ALIAS=E02, USAGE=A10, ACTUAL=A12, $
WebFOCUS |