Using SQL Translator Commands

Syntax: How to Use SQL Translator Commands

SQL 
sql statement;
[ECHO|FILE]
[TABLE phrases]
END

where:

SQL

Is the SQL command identifier, which invokes the SQL Translator.

Note: The SQL command components must appear in the order represented above.

sql statement

Is a supported SQL statement. The statement must be terminated by a semicolon (;). It can continue for more than one line. See Supported SQL Statements.

Within the SQL statement, field names are limited to 48 characters (an ANSI standard Level 2 limitation). View names generated through the SQL CREATE VIEW statement are limited to 18 characters and subqueries can be nested up to 15 levels deep. Correlated subqueries are not supported by FOCUS and other non-relational data sources.

ECHO

Are optional debugging phrases that capture the generated TABLE request. These options are placed after the SQL statement.

FILE [name]

Writes the translated TABLE phrases to the named procedure. If you do not supply a file name, a default name is assigned when the request runs. The file is then deleted.

TABLE phrases

Are optional TABLE formatting phrases. See TABLE Formatting Phrases in SQL Requests.

END or QUIT

Is required to terminate the procedure.

Example: Using SQL Translator Commands

The following request contains an SQL statement and TABLE formatting commands:

SQL
SELECT BODYTYPE, AVG(MPG), SUM(SALES)
FROM CAR
WHERE RETAIL_COST > 5000
GROUP BY BODYTYPE;
TABLE HEADING CENTER
"AVERAGE MPG AND TOTAL SALES PER BODYTYPE"
END

Reference: TABLE Formatting Phrases in SQL Requests

You can include TABLE formatting phrases in an SQL request, subject to the following rules:

Use TABLE formatting phrases with SELECT and UNION only.
Introduce the formatting phrases with the word TABLE.
You may specify headings and footings, describe actions with an ON phrase, or use the ON TABLE SET command. Additionally, you can use ON TABLE HOLD or ON TABLE PCHOLD to create an extract file. You can also specify READLIMIT and RECORDLIMIT tests.
For details on headings and footings, see Using Headings, Footings, Titles, and Labels.

For details on ON TABLE HOLD or ON TABLE PCHOLD, see Saving and Reusing Your Report Output.
You cannot specify additional display fields, ACROSS fields, WHERE or IF criteria (other than READLIMIT or RECORDLIMIT tests), or calculated values. BY phrases are ignored.

The SQL SELECT Statement

In this section:

Using the SQL SELECT Statement Without a FROM Clause

The SQL SELECT statement translates into one or more TABLE PRINT or TABLE SUM commands, depending on whether individual field display or aggregation is applied in the request. See Displaying Report Data.

The SQL statement SELECT * translates to a PRINT of every field in the Master File, and uses all of the fields of the Cartesian product. This is a quick way to display a file, provided it fits in a reasonable number of screens for display, or provided you use ON TABLE HOLD or ON TABLE PCHOLD to retain retrieved data in a file for reuse. See Saving and Reusing Your Report Output.

SQL functions (such as COUNT, SUM, MAX, MIN, AVG) are supported in SELECT lists and HAVING conditions. Expressions may be used as function arguments.

The function COUNT (*) translates to a count of the number of records produced by printing all fields in the Master File. This is the same as counting all rows in the Cartesian product that results from a SELECT on all fields.

Whenever possible, expressions in the SQL WHERE predicate are translated into corresponding WHERE criteria in the TABLE request. Expressions in SELECT lists generate virtual fields. The SQL HAVING clauses also translate into corresponding WHERE TOTAL criteria in the TABLE request. The SQL LIKE operator is translated directly into the corresponding LIKE operator in the WHERE criteria of the TABLE request. For details on record selection in TABLE requests, see Selecting Records for Your Report.

Only subqueries based on equality, when the WHERE expression is compared to a subquery by using an equal (=) sign, are supported. For example: WHERE field = (SELECT ...).

The SQL UNION operator translates to a TABLE request that creates a HOLD file for each data source specified, followed by a MATCH command with option HOLD OLD-OR-NEW, which combines records from both the first (old) data source and the second (new) data source. See Merging Data Sources.

For related information, see Supported SQL Statements and Use SQL Translator Commands.

Using the SQL SELECT Statement Without a FROM Clause

SELECT without a FROM clause is supported for returning a one-row answer set consisting of one or more constant values. No Master File is needed for issuing this type of SELECT. One use for this syntax is to test functions. For example, the following SQL SELECT returns a literal value and the results of two function calls:

SQL
SELECT 'MOD' AS FUNCTION, MOD(6,3) AS MOD1, MOD(5,3) AS MOD2;
TABLE
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

SQL Joins

How to:

Create an Inner Join
Create an Outer Join

Reference:

Join Name Assignments From the SQL Translator
SQL Join Considerations

When performing SQL joins, the formats of the joined fields must be the same. Join fields need not be indexed, and non-equijoins are supported.

Recursive, outer, and inner joins are supported. Inner join is the default.

Syntax: How to Create an Inner Join

Two syntax variations are supported for inner joins.

Variation 1

SQL
SELECT fieldlist FROM file1 [alias1], file2 [alias2]
[WHERE where_condition];
END

Variation 2

SQL
SELECT fieldlist FROM file1 [alias1] INNER JOIN file2 [alias2]
ON join_condition [INNER JOIN ...]
[WHERE where_condition];
END

where:

fieldlist

Identifies which fields are retrieved from which data sources.

Joined fields in the SQL WHERE predicate must be qualified if the names are not unique. Specify them with their corresponding file names or file aliases. For example:

{file1|alias1}.field1, {file2|alias2}.field2

FROM

Introduces the data sources to be joined.

file1, file2

Are the data sources to be joined.

alias1, alias2

Are optional alternate names for the data sources to be joined.

where_condition

Is an optional selection condition for the joined answer set. Joined rows that do not satisfy this condition are eliminated from the returned answer set. If omitted in Variation 1, the answer set is the Cartesian product of the two data sources.

join_condition

Is the join condition.

Syntax: How to Create an Outer Join

SQL
SELECT fieldlist FROM file1 {LEFT|RIGHT|FULL} JOIN file2 
ON join_condition [{LEFT|RIGHT|FULL} JOIN ...]
WHERE where_condition 
END

where:

fieldlist

Identifies which fields are to be retrieved from which data sources.

Joined fields in the SQL WHERE predicate must be qualified if the names are not unique. Specify them with their corresponding file names or file aliases. For example:

{file1|alias1}.field1, {file2|alias2}.field2

FROM

Introduces the data sources to be joined.

file1, file2

Are the data sources to be joined.

alias1, alias2

Are optional alternate names for the data sources to be joined.

join_condition

Is the join condition. The condition must specify equality. For example, T1.A=T2.B.

where_condition

Is an optional selection condition for the joined answer set. Joined rows that do not satisfy this condition are eliminated from the returned answer set.

Reference: Join Name Assignments From the SQL Translator

Joins issued by the SQL Translator are assigned names in the format:

SQLJNMnn

where:

SQLJNM: Is the SQL Translator join prefix.
nn: Is a number between 01 and 16 assigned in the order in which the joins are created (FOCUS supports a maximum of 16 joins). The first join has the AS name SQLJNM01, the second join is named SQLJNM02, and so on, up to SQLJNM16.

All joins are automatically created and cleared by the SQL Translator. No user-specified joins are affected.

Example: Using Qualified Field Names in SQL Joins

In the following statement, T.A and U.B are qualified field names:

SQL
 SELECT T.A, T.B
 FROM T, U
 WHERE T.A = U.B;
END

Example: Using Recursive SQL Joins

In the following statement, A and B are aliases for the same data source, CAR. The output from CAR is pairs of B values that have the same A values:

SQL
   SELECT A.SEATS, B.SEATS
   FROM CAR A, CAR B
   WHERE A.MODEL = B.MODEL;
END

Note that all field names in the SELECT clause must be unique or qualified.

Example: Using SQL Full Outer Joins

In the following statement, B, C, and D are aliases for different data sources:

SQL
SELECT   
       B.FIELD1 AS B_FIELD1, B.FIELD2 AS B_FIELD2, 
       D.FIELD1 AS D_FIELD1, D.FIELD2 AS D_FIELD2   
FROM   
((FILE1 B FULL OUTER JOIN FILE2 C ON B.FIELD2 = C.FIELD2 )   
           FULL OUTER JOIN FILE3 D ON C.FIELD2 = D.FIELD2 )   
WHERE B.FIELD1 < 2   
END

Multiple FULL OUTER JOINS are supported. However, they generate from a few to many temporary HOLD files.

Reference: SQL Join Considerations

In standard SQL, WHERE field='a' selects records where the field has the value 'a' or 'A'. The SQL Translator is case-sensitive and returns the exact value requested (in this case, 'a' only).
The SQL comparison operators ANY, SOME, and ALL are supported, with the exception of =ALL, <>ANY, and <>SOME.
Sub-selects are not supported in HAVING conditions.
In a multi-segment structure, parent segments are omitted from reports if no instances of their descendant segments exist. This is an inner join.
The SQL Translator applies optimization techniques when constructing joins. See Index Optimized Retrieval.

For related information about index optimization and optimized join statements, see your Server documentation.

SQL CREATE TABLE and INSERT INTO Commands

Reference:

Usage Notes for CREATE TABLE and INSERT INTO Commands

SQL Translator supports the commands CREATE TABLE and INSERT INTO table:

CREATE TABLE creates a new data source table. It only generates single-segment Master Files.
INSERT INTO inserts a row or block of rows into a table or view. Single-record insert with actual data values is supported.

These commands enable you to create tables to enhance reporting efficiency.

Note: When applications are enabled, the Master File and data source are written to the APPHOLD directory. When applications are disabled, the Master File and data source are written to the TEMP directory.

Reference: Usage Notes for CREATE TABLE and INSERT INTO Commands

According to normal SQL data definition syntax, each CREATE TABLE or INSERT INTO statement must terminate with a semicolon.
The CREATE TABLE command supports the INTEGER, SMALLINT, FLOAT, CHARACTER, DATE, TIME, TIMESTAMP, DECIMAL, DOUBLE PRECISION and REAL data types. Decimals are rounded in the DOUBLE PRECISION and REAL data types.
When using the CREATE TABLE and INSERT INTO commands, the data type FLOAT should be declared with a precision and used in an INSERT INTO command without the 'E' designation. This requires the entire value to be specified without an exponent.
The CHECK and DEFAULT options are not supported with the CREATE TABLE command.

Example: Creating a Table With Single-Record Insert

The following shows a single-record insert, creating the table U with one record:

-* Single-record insert example.
-*
SQL
CREATE TABLE U (A INT, B CHAR(6), C CHAR(6), X INT, Y INT);
END
SQL
INSERT INTO U (A,B,C,X,Y) VALUES (10, '123456','654321', 10, 15);
END

SQL CREATE VIEW and DROP VIEW Commands

How to:

Create a View

A view is a transient object that inherits most of the characteristics of a table. Like a table, it is composed of rows and columns:

CREATE VIEW creates views. Note that it does not put the view in the system catalog.
DROP VIEW explicitly removes transient tables and views from the environment.

Tip: To use a view, issue a SELECT from it. You cannot issue a TABLE request against the view because the view is not extracted as a physical FOCUS data source. To create a HOLD file for extracted data, specify ON TABLE HOLD after the SQL statements. For details on creating HOLD files, see Saving and Reusing Your Report Output.

Syntax: How to Create a View

The SQL Translator supports the following SQL statement:

CREATE VIEW viewname AS subquery ;

where:

viewname

Is the name of the view.

subquery

Is a SELECT statement that nests inside:

A WHERE, HAVING, or SELECT clause of another SELECT.
An UPDATE, DELETE, or INSERT statement.
Another subquery.

Example: Creating and Reporting From an SQL View

The following example creates a view named XYZ:

SQL
CREATE VIEW XYZ
 AS SELECT CAR, MODEL
 FROM CAR;
END

To report from the view, issue:

SQL
 SELECT CAR, MODEL
 FROM XYZ;
END

According to normal SQL data definition syntax, each CREATE VIEW statement must terminate with a semicolon.

Example: Dropping an SQL View

The following request removes the XYZ view:

SQL
 DROP VIEW XYZ;
END

Cartesian Product Style Answer Sets

The SQL Translator automatically generates Cartesian product style answer sets unless you explicitly turn this feature off. However, it is advisable to leave the CARTESIAN setting on, since turning it off does not comply with ANSI standards. For details on the SET CARTESIAN command, see Merging Data Sources.

Continental Decimal Notation (CDN)

Continental Decimal Notation displays numbers using a comma to mark the decimal position and periods for separating significant digits into groups of three. This notation is available for SQL Translator requests.

Example: Using CDN to Separate Digits

The following example creates a column defined as 1.2 + SEATS:

SET CDN=ON
SQL
   SELECT SEATS + 1,2
   FROM CAR;
END

Specifying Field Names in SQL Requests

Specify fields in an SQL request using:

Delimited identifiers. A field name may contain (but not begin with) the symbols ., #, @, _, and $. You must enclose such field names in double quotation marks when referring to them.
Qualified field names. Qualify a field name with file and file alias names. File alias names are described in the discussion of joins in SQL Joins. See the Describing Data With TIBCO WebFOCUS® Languagemanual for more information.
Field names with embedded blanks and special characters. A SELECT list can specify field names with embedded blanks or other special characters. You must enclose such field names in double quotation marks. Special characters are any characters not listed as delimited identifiers, and not contained in the national character set of the installed FOCUS environment.

Example: Specifying a Field Name With a Delimited Identifier

The following field identifier can be included in a request:

"COUNTRY.NAME"

Example: Qualifying a Delimited Field Name

To qualify the delimited field name COUNTRY.NAME with its file name, use:

CAR."COUNTRY.NAME"

SQL UNION, INTERSECT, and EXCEPT Operators

The SQL UNION, INTERSECT, and EXCEPT operators generate MATCH logic. The number of files that can participate is determined by the MATCH limit. UNION with parentheses is supported.

SELECT A UNION SELECT B retrieves rows in A or B or both. (This is equivalent to the MATCH phrase OLD-OR-NEW.)
INTERSECT retrieves rows in both A and B. (This is equivalent to the MATCH phrase OLD-AND-NEW.)
EXCEPT retrieves rows in A, but not B. (This is equivalent to the MATCH phrase OLD-NOT-NEW.)

Match logic merges the contents of your data sources. See Merging Data Sources.

Numeric Constants, Literals, Expressions, and Functions

The SQL SELECT list, WHERE predicate, and HAVING clause can include numeric constants, literals enclosed in single quotation marks, expressions, and any scalar functions. Internally, a virtual field is created for each of these in the SELECT list. The value of the virtual field is provided in the answer set.