Creating a Subquery or Sequential File With HOLD FORMAT SQL_SCRIPT

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.


Top of page

x
Syntax: How to Create an SQL Script or Sequential File Using HOLD FORMAT SQL_SCRIPT
ON TABLE HOLD AS script_name FORMAT SQL_SCRIPT

where:

script_name

Is the name of the .sql file or the .ftm file created as a result of the HOLD FORMAT SQL_SCRIPT command.



Example: Creating an SQL Script File Using HOLD FORMAT SQL_SCRIPT

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


Example: Creating a Sequential File Using HOLD FORMAT SQL_SCRIPT

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