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 TIBCO FOCUS® Developing Applica 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 TIBCO WebFOCUS® Language manual.

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_LITE
SUM BUSINESS_REGION STATE_PROV_CODE_ISO_3166_2
BY BUSINESS_REGION NOPRINT BY STATE_PROV_CODE_ISO_3166_2 NOPRINT
WHERE BUSINESS_REGION EQ 'North America' OR 'EMEA'
WHERE STATE_PROV_CODE_ISO_3166_2 EQ 'AR' OR 'IA' OR 'KS' OR 'KY' OR 'WY' OR 'CT' OR 'MA' OR '04' OR '11' OR '14'
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(T3."BUSINESS_REGION") AS "VB001_MAX_BUSINESS_REGION",	 MAX(T3."STATE_PROV_CODE_ISO_3166_2") 
AS "VB002_MAX_STATE_PROV_CODE_ISO_"	 FROM 	wrd_wf_retail_geography T3	 
WHERE 	(T3."STATE_PROV_CODE_ISO_3166_2" IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT', 'MA', '04', '11', '14', 'NJ', 'NY', 'RI')) 
AND 	(T3."BUSINESS_REGION" IN('North America', 'EMEA'))	 GROUP BY 	T3."BUSINESS_REGION",	T3."STATE_PROV_CODE_ISO_3166_2"

The retail_script.mas Master File follows:

FILENAME=RETAIL_SCRIPT, SUFFIX=MSODBC  , $
  SEGMENT=RETAIL_SCRIPT, SEGTYPE=S0, $
    FIELDNAME=BUSINESS_REGION, ALIAS=VB001_MAX_BUSINESS_REGION, USAGE=A15V, ACTUAL=A15V,
      MISSING=ON,
      TITLE='Customer,Business,Region', $
    FIELDNAME=STATE_PROV_CODE_ISO_3166_2, ALIAS=VB002_MAX_STATE_PROV_CODE_ISO_, USAGE=A5V, ACTUAL=A5V,
      MISSING=ON,
      TITLE='Customer,State,Province,ISO-3166-2,Code', $

The retail_script.acx Access File follows: 

SEGNAME=RETAIL_SCRIPT, 
   CONNECTION=CON01, 
   DATASET=RETAIL_SCRIPT.SQL, 
   SUBQUERY=Y, $

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=DATREC  , IOTYPE=BINARY, $
  SEGMENT=EMPVALUE, SEGTYPE=S0, $
    FIELDNAME=LAST_NAME, ALIAS=E01, USAGE=A15, ACTUAL=A15, $
    FIELDNAME=FIRST_NAME, ALIAS=E02, USAGE=A10, ACTUAL=A10, $
    FIELDNAME=NULLFLAG, ALIAS=__NULLFLAG__, USAGE=A2, ACTUAL=A2B, ACCESS_PROPERTY=(INTERNAL), $