A table-reference specifies an intermediate result table.
>>-+-| single-table-reference |------+------------------------->< +-| single-view-reference |-------+ +-| single-nickname-reference |---+ +-| only-table-reference |--------+ +-| outer-table-reference |-------+ +-| analyze_table-expression |----+ +-| nested-table-expression |-----+ +-| data-change-table-reference |-+ +-| table-function-reference |----+ +-| collection-derived-table |----+ +-| xmltable-expression |---------+ | (1) | '-| joined-table |----------------' single-table-reference .------------------------------. V | |--table-name----+--------------------------+-+--+------------------------+--> '-| period-specification |-' '-| correlation-clause |-' >--+------------------------+-----------------------------------| '-| tablesample-clause |-' single-view-reference .------------------------------. V | |--view-name----+--------------------------+-+--+------------------------+--| '-| period-specification |-' '-| correlation-clause |-' single-nickname-reference |--nickname--+------------------------+-------------------------| '-| correlation-clause |-' only-table-reference |--ONLY--(--+-table-name-+--)--+------------------------+-------| '-view-name--' '-| correlation-clause |-' outer-table-reference |--OUTER--(--+-table-name-+--)--+------------------------+------| '-view-name--' '-| correlation-clause |-' analyze_table-expression |--+-table-name-+-----------------------------------------------> '-view-name--' >--ANALYZE_TABLE--(--| implementation-clause |--)---------------| nested-table-expression |--+-----------------------------------------------+--(fullselect)--+------------------------+--| | (2) | '-| correlation-clause |-' '-LATERAL------+------------------------------+-' '-| continue-handler |--WITHIN-' data-change-table-reference |--+-+-FINAL-+--TABLE--(--insert-statement--)----------+--+------------------------+--| | '-NEW---' | '-| correlation-clause |-' +-+-FINAL-+--TABLE--(--searched-update-statement--)-+ | +-NEW---+ | | '-OLD---' | '-OLD TABLE--(--searched-delete-statement--)--------' table-function-reference |--TABLE--(--function-name--(--+----------------+--)--+----------------------------------+--)--> | .-,----------. | '-| table-UDF-cardinality-clause |-' | V | | '---expression-+-' >--+----------------------------------+-------------------------| +-| correlation-clause |-----------+ | (3) | '-| typed-correlation-clause |-----' collection-derived-table |--UNNEST-table-function--+---------------------+--+------------------------+--| | (4) | '-| correlation-clause |-' '-WITH ORDINALITY-----' xmltable-expression (5) |--xmltable-function------+------------------------+------------| '-| correlation-clause |-' period-specification |--FOR--+-SYSTEM_TIME---+--+-AS OF--value-----------------+-----| '-BUSINESS_TIME-' +-FROM--value1--TO--value2-----+ '-BETWEEN--value1--AND--value2-' correlation-clause .-AS-. |--+----+--correlation-name--+-----------------------+----------| | .-,-----------. | | V | | '-(----column-name-+--)-' tablesample-clause |--TABLESAMPLE--+-BERNOULLI-+--(--numeric-expression1--)--------> '-SYSTEM----' >--+---------------------------------------+--------------------| '-REPEATABLE--(--numeric-expression2--)-' implementation-clause |--IMPLEMENTATION--'--string--'---------------------------------| continue-handler .-,----------------------------. V | |--RETURN DATA UNTIL----| specific-condition-value |-+----------| table-UDF-cardinality-clause |--+-CARDINALITY--integer-constant------------+-----------------| '-CARDINALITY MULTIPLIER--numeric-constant-' typed-correlation-clause .-AS-. |--+----+--correlation-name--+----------------------------------+--| | .-,----------------------. | | V | | '-(----column-name--data-type-+--)-' specific-condition-value .-VALUE-. |--FEDERATED--SQLSTATE--+-------+--string-constant--+-------------------------------+--| | .-,----------------. | | V | | '-SQLCODE----integer-constant-+-'
Each table-name specified as a table-reference must identify an existing table at the application server or an existing table at a remote server specified using a remote-object-name. The intermediate result table is the result of the table. If the table-name references a typed table, the intermediate result table is the UNION ALL of the table with all its subtables, with only the columns of the table-name. A period-specification can be used with a temporal table to specify the period from which the rows are returned as the intermediate result table. A tablesample-clause can be used to specify that a sample of the rows be returned as the intermediate result table.
table-name FOR SYSTEM_TIME AS OF CTST
If
the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null
value CTBT and table-name identifies
an application-period temporal table, the table reference is executed
as if it contained the following specification with the special register
set to the null value: table-name FOR BUSINESS_TIME AS OF CTBT
Each nickname specified as a table-reference must identify an existing nickname at the application server. The intermediate result table is the result of the nickname.
The use of ONLY(table-name) or ONLY(view-name) means that the rows of the applicable subtables or subviews are not included in the intermediate result table. If the table-name used with ONLY does not have subtables, then ONLY(table-name) is equivalent to specifying table-name. If the view-name used with ONLY does not have subviews, then ONLY(view-name) is equivalent to specifying view-name.
The use of ONLY requires the SELECT privilege on every subtable of table-name or subview of view-name.
The use of OUTER(table-name) or OUTER(view-name) represents a virtual table. If the table-name or view-name used with OUTER does not have subtables or subviews, then specifying OUTER is equivalent to not specifying OUTER. If the table-name does have subtables, the intermediate result table from OUTER(table-name) is derived from table-name as follows:
If the view-name does have subviews, the intermediate result table from OUTER(view-name) is derived from view-name as follows:
The use of OUTER requires the SELECT privilege on every subtable of table-name or subview of view-name.
The routine source table has a defined structure (see the examples at the end of the "analyze_table-expression" section) and, in a partitioned database environment, must be on the catalog database partition. The table cannot be a global temporary table. The MODELDS2 column for a particular row must not be empty or contain the null value. If the value of the MODELFORMATS or MODELMETADATA column is not null, the value must have a length greater than 0. If you do not specify a table schema name, the value of the CURRENT SCHEMA special register is used.
IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
ROUTINE_SOURCE_NAME=SCORING_FUN1;'
If the table
name, schema name, or algorithm name contains lowercase or mixed-case
letters, specify delimited identifiers, as shown in the following
example:IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE="ETLin"."Source_Table";
ROUTINE_SOURCE_NAME="Scoring_Fun1";'
The following examples show you how to use the ANALYZE_TABLE expression.
CREATE TABLE ETLIN.SOURCE_TABLE (
MODELNAME VARCHAR(128) NOT NULL PRIMARY KEY,
MODELDS2 BLOB(4M) NOT NULL,
MODELFORMATS BLOB(4M),
MODELMETADATA BLOB(4M)
);
The MODELNAME column contains the name of the algorithm.
The MODELDS2 column contains the DS2 source code that implements the
algorithm. The MODELFORMATS column contains the aggregated SAS format
definition that the algorithm requires. If the algorithm does not
require a SAS format, this column contains the null value. The MODELMETADATA
column contains any additional metadata that the algorithm requires.
If the algorithm does not require any additional metadata, this column
contains the null value. If the SAS EP installer creates the table,
it might include additional columns. WITH sas_score_in (c1,c2) AS
(SELECT c1,c2 FROM t1)
SELECT *
FROM sas_score_in ANALYZE_TABLE(
IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
ROUTINE_SOURCE_NAME=SCORING_FUN1;');
SELECT *
FROM t2 ANALYZE_TABLE(
IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
ROUTINE_SOURCE_NAME=SCORING_FUN2;');
SELECT *
FROM v1 ANALYZE_TABLE(
IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
ROUTINE_SOURCE_NAME=SCORING_FUN3;')
ORDER BY 1;
A fullselect in parentheses is called a nested table expression. The intermediate result table is the result of that fullselect. The columns of the result do not need unique names, but a column with a non-unique name cannot be explicitly referenced. If LATERAL is specified, the fullselect can include correlated references to results columns of table references specified to the left of the nested table expression. If the nested table expression involves data from a federated data source, a continue-handler can be specified to tolerate certain error conditions from the data source.
A data-change-table-reference clause specifies an intermediate result table. This table is based on the rows that are directly changed by the searched UPDATE, searched DELETE, or INSERT statement that is included in the clause. A data-change-table-reference can be specified as the only table-reference in the FROM clause of the outer fullselect that is used in a select-statement, a SELECT INTO statement, or a common table expression. A data-change-table-reference can be specified as the only table reference in the only fullselect in a SET Variable statement (SQLSTATE 428FL). The target table or view of the data change statement is considered to be a table or view that is referenced in the query; therefore, the authorization ID of the query must have SELECT privilege on that target table or view. A data-change-table-reference clause cannot be specified in a view definition, materialized query table definition, or FOR statement (SQLSTATE 428FL).
The target of the UPDATE, DELETE, or INSERT statement cannot be a temporary view defined in a common table expression (SQLSTATE 42807) or a nickname (SQLSTATE 25000).
The content of the intermediate result table for a data-change-table-reference is determined when the cursor opens. The intermediate result table contains all manipulated rows, including all the columns in the specified target table or view. All the columns of the target table or view for an SQL data change statement are accessible using the column names from the target table or view. If an INCLUDE clause was specified within a data change statement, the intermediate result table will contain these additional columns.
Example 1: SELECT c1
FROM TABLE( tf1('Zachary') ) AS z
WHERE c2 = 'FLORIDA';
Example 2: SELECT c1
FROM TABLE( tf2 (:hostvar1, CURRENT DATE) ) AS z;
Example 3: SELECT c1
FROM t
WHERE c2 IN
(SELECT c3 FROM
TABLE( tf5(t.c4) ) AS z -- correlated reference
) -- to previous FROM clause
Example 4: SELECT c1
FROM TABLE( tf6('abcd') ) -- tf6 is a generic
AS z (c1 int, c2 varchar(100)) -- java table function
Example 1: SELECT c1
FROM TABLE( tfmod('Jones') ) AS z
Example 2: SELECT c1
FROM t1, t2, TABLE( tfmod(t1.c1, t2.c1) ) AS z
Example 3: SET var =
(SELECT c1
FROM TABLE( tfmod('Jones') ) AS z
Example 4: RETURN SELECT c1
FROM TABLE( tfmod('Jones') ) AS z
Example 5: WITH v1(c1) AS
(SELECT c1
FROM TABLE( tfmod(:hostvar1) ) AS z)
SELECT c1
FROM v1, t1 WHERE v1.c1 = t1.c1
Example 6: SELECT z.*
FROM t1, t2, TABLE( tfmod(t1.c1, t2.c1) )
AS z (col1 int)
A collection-derived-table can be used to convert the elements of an array into values of a column in separate rows. If WITH ORDINALITY is specified, an extra column of data type INTEGER is appended. This column contains the position of the element in the array. The columns can be referenced in the select list and the in rest of the subselect by using the names specified for the columns in the correlation-clause. The collection-derived-table clause can be used only in a context where arrays are supported (SQLSTATE 42887). See the "UNNEST table function" for details.
A joined-table specifies an intermediate result set that is the result of one or more join operations. For more information, see joined-table.
A period-specification identifies an intermediate result table consisting of the rows of the referenced table where the period matches the specification. A period-specification can be specified following the name of a temporal table or the name of a view. The same period name must not be specified more than once for the same table reference (SQLSTATE 428HY). The rows of the table reference are derived by application of the period specifications.
If the table is a system-period temporal table and a period-specification for the SYSTEM_TIME period is not specified, the table reference includes all current rows and does not include any historical rows of the table. If the table is an application-period temporal table and a period-specification for the BUSINESS_TIME period is not specified, the table reference includes all rows of the table. If the table is a bitemporal table and a period-specification is not specified for both SYSTEM_TIME and BUSINESS_TIME, the table reference includes all current rows of the table and does not include any historical rows of the table.
If the table reference is a single-view-reference, the rows of the view reference are derived by application of the period specifications to all of the temporal tables accessed when computing the result table of the view. If the view does not access any temporal table, then the period-specification has no effect on the result table of the view. If period-specification is used, the view definition or any view definitions referenced when computing the result table of the view must not include any references to compiled SQL functions or external functions with a data access indication other than NO SQL (SQLSTATE 428HY).
If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a value other than the null value, then a period-specification that references SYSTEM_TIME must not be specified for the table reference or view reference, unless the value in effect for the SYSTIMESENSITIVE bind option is NO (SQLSTATE 428HY).
If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a value other than the null value, then a period specification that references BUSINESS_TIME must not be specified for the table reference or view reference, unless the value in effect for the BUSTIMESENSITIVE bind option is NO (SQLSTATE 428HY).
Specifies that the SYSTEM_TIME period is used for the period-specification. If the clause is specified following a table-name, the table must be a system-period temporal table (SQLSTATE 428HY). FOR SYSTEM_TIME must not be specified if the value of the CURRENT TEMPORAL SYSTEM_TIME special register is not the null value and the SYSTIMESENSITIVE bind option is set to YES (SQLSTATE 428HY).
Specifies that the BUSINESS_TIME period is used for the period-specification. If the clause is specified following a table-name, BUSINESS_TIME must be a period defined in the table (SQLSTATE 4274M). FOR BUSINESS_TIME must not be specified if the value of the CURRENT TEMPORAL BUSINESS_TIME special register is not the null value and the BUSTIMESENSITIVE bind option is set to YES (SQLSTATE 428HY).
The value, value1, and value2 expressions return the null value or a value of one of the following built-in data types (SQLSTATE 428HY): a DATE, a TIMESTAMP, or a character string that is not a CLOB or DBCLOB. If the argument is a character string, it must be a valid character string representation of a timestamp or a date (SQLSTATE 22007). For the valid formats of string representations of timestamp values, see the section "String representations of datetime values" in the topic "Datetime values".
Specifies that the table reference includes each row for which the value of the begin column for the specified period is less than or equal to value, and the value of the end column for the period is greater than value. If value is the null value, the table reference is an empty table.
SELECT coverage FROM policy_info FOR BUSINESS_TIME
AS OF '2010-08-31' WHERE policy_id = '100'
Specifies that the table reference includes rows that exist for the period specified from value1 to value2. A row is included in the table reference if the value of the begin column for the specified period in the row is less than value2, and the value of the end column for the specified period in the row is greater than value1. The table reference contains zero rows if value1 is greater than or equal to value2. If value1 or value2 is the null value, the table reference is an empty table.
SELECT coverage FROM policy_info FOR BUSINESS_TIME
FROM '2009-01-01' TO '2010-01-01' WHERE policy_id = '100'
Specifies that the table reference includes rows in which the specified period overlaps at any point in time between value1 and value2. A row is included in the table reference if the value of the begin column for the specified period in the row is less than or equal to value2 and the value of the end column for the specified period in the row is greater than value1. The table reference contains zero rows if value1 is greater than value2. If value1 is equal to value2, the expression is equivalent to AS OF value1. If value1 or value2 is the null value, the table reference is an empty table.
SELECT coverage FROM policy_info FOR BUSINESS_TIME
BETWEEN '2008-01-01' AND '2008-12-31' WHERE policy_id = '100'
If a correlation-clause clause does not follow a function-name reference, xmltable-expression expression, nested table expression, or data-change-table-reference reference, or if a typed-correlation-clause clause does not follow a function-name reference, then there is no exposed name for that table reference.
The optional tablesample-clause can be used to obtain a random subset (a sample) of the rows from the specified table-name, rather than the entire contents of that table-name, for this query. This sampling is in addition to any predicates that are specified in the where-clause. Unless the optional REPEATABLE clause is specified, each execution of the query will usually yield a different sample, except in degenerate cases where the table is so small relative to the sample size that any sample must return the same rows. The size of the sample is controlled by the numeric-expression1 in parentheses, representing an approximate percentage (P) of the table to be returned.
The method by which the sample is obtained is specified after the TABLESAMPLE keyword, and can be either BERNOULLI or SYSTEM. For both methods, the exact number of rows in the sample might be different for each execution of the query, but on average is approximately P percent of the table, before any predicates further reduce the number of rows.
The table-name must be a stored table. It can be a materialized query table (MQT) name, but not a subselect or table expression for which an MQT has been defined, because there is no guarantee that the database manager will route to the MQT for that subselect.
Semantically, sampling of a table occurs before any other query processing, such as applying predicates or performing joins. Repeated accesses of a sampled table within a single execution of a query (such as in a nested-loop join or a correlated subquery) will return the same sample. More than one table can be sampled in a query.
BERNOULLI sampling considers each row individually. It includes each row in the sample with probability P/100 (where P is the value of numeric-expression1), and excludes each row with probability 1 - P/100, independently of the other rows. So if the numeric-expression1 evaluated to the value 10, representing a ten percent sample, each row would be included with probability 0.1, and excluded with probability 0.9.
SYSTEM sampling permits the database manager to determine the most efficient manner in which to perform the sampling. In most cases, SYSTEM sampling applied to a table-name means that each page of table-name is included in the sample with probability P/100, and excluded with probability 1 - P/100. All rows on each page that is included qualify for the sample. SYSTEM sampling of a table-name generally executes much faster than BERNOULLI sampling, because fewer data pages are retrieved. However, SYSTEM sampling can often yield less accurate estimates for aggregate functions, such as SUM(SALES), especially if the rows of table-name are clustered on any columns referenced in that query. The optimizer might in certain circumstances decide that it is more efficient to perform SYSTEM sampling as if it were BERNOULLI sampling. An example is when a predicate on table-name can be applied by an index and is much more selective than the sampling rate P.
The numeric-expression1 specifies the size of the sample to be obtained from table-name, expressed as a percentage. It must be a constant numeric expression that cannot contain columns. The expression must evaluate to a positive number that is less than or equal to 100, but can be between 1 and 0. For example, a value of 0.01 represents one one-hundredth of a percent, meaning that 1 row in 10 000 is sampled, on average. A numeric-expression1 that evaluates to 100 is handled as if the tablesample-clause were not specified. If numeric-expression1 evaluates to the null value, or to a value that is greater than 100 or less than 0, an error is returned (SQLSTATE 2202H).
It is sometimes desirable for sampling to be repeatable from one execution of the query to the next; for example, during regression testing or query debugging. This can be accomplished by specifying the REPEATABLE clause. The REPEATABLE clause requires the specification of a numeric-expression2 in parentheses, which serves the same role as the seed in a random number generator. Adding the REPEATABLE clause to the tablesample-clause of any table-name ensures that repeated executions of that query (using the same value for numeric-expression2) return the same sample, assuming that the data itself has not been updated, reorganized, or repartitioned. To guarantee that the same sample of table-name is used across multiple queries, use of a global temporary table is recommended. Alternatively, the multiple queries can be combined into one query, with multiple references to a sample that is defined using the WITH clause.
SELECT * FROM Sales
TABLESAMPLE BERNOULLI(10)
SELECT SUM(Sales.Revenue) / (0.01)
FROM Sales TABLESAMPLE SYSTEM(1)
WHERE Sales.RegionName = 'Northeast'
GROUP BY Sales.ProductCategory
SELECT SUM(Sales.Revenue) / (0.01)
FROM Sales TABLESAMPLE SYSTEM(1) REPEATABLE(3578231)
WHERE Sales.RegionName = 'Northeast'
GROUP BY Sales.ProductCategory
The product of the specified CARDINALITY MULTIPLIER numeric-constant and the reference cardinality value are used by the database server as the expected number of rows that are returned by the table function reference.
In this case, numeric-constant can be in the integer, decimal, or floating-point format. The value must be greater than or equal to zero. If the decimal number notation is used, the number of digits can be up to 31. An integer value is treated as a decimal number with no fraction. If zero is specified or the computed cardinality is less than 1, the cardinality of the reference to the user-defined table function is assumed to be 1.
The value in the CARDINALITY column of SYSSTAT.ROUTINES for the table function name is used as the reference cardinality value. If no value is set in the CARDINALITY column of SYSSTAT.ROUTINES, a finite value is assumed as its default value for the reference cardinality value. This finite value is the same value that is assumed for tables for which the RUNSTATS utility has not gathered statistics.
Only a numeric constant can follow the keyword CARDINALITY or CARDINALITY MULTIPLIER. A host variable or parameter marker is not supported. Specifying a cardinality value in a table function reference does not change the CARDINALITY column value for the function in the SYSSTAT.ROUTINES catalog view.
The CARDINALITY value for external and compiled SQL user-defined table functions can be changed by updating the CARDINALITY column in the SYSSTAT.ROUTINES catalog view. The CARDINALITY value for an external table function can also be initialized by specifying the CARDINALITY option in the CREATE FUNCTION (external table) statement when a user-defined table function is created.
SQL column data type | Equivalent Java™ data type |
---|---|
SMALLINT | short |
INTEGER | int |
BIGINT | long |
REAL | float |
DOUBLE | double |
DECIMAL(p,s) | java.math.BigDecimal |
NUMERIC(p,s) | java.math.BigDecimal |
CHAR(n) | java.lang.String |
CHAR(n) FOR BIT DATA | COM.ibm.db2.app.Blob |
VARCHAR(n) | java.lang.String |
VARCHAR(n) FOR BIT DATA | COM.ibm.db2.app.Blob |
GRAPHIC(n) | java.lang.String |
VARGRAPHIC(n) | String |
BLOB(n) | COM.ibm.db2.app.Blob |
CLOB(n) | COM.ibm.db2.app.Clob |
DBCLOB(n) | COM.ibm.db2.app.Clob |
DATE | String |
TIME | String |
TIMESTAMP | String |
XML AS CLOB(n) | COM.ibm.db2.jcc.DB2Xml |
Certain errors that occur within a nested-table-expression can be tolerated, and instead of returning an error, the query can continue and return a result. This is referred to as an error tolerant nested-table-expression.
Specifying the RETURN DATA UNTIL clause will cause any rows that are returned from the fullselect before the indicated condition is encountered to make up the result set from the fullselect. This means that a partial result set (which can also be an empty result set) from the fullselect is acceptable as the result for the nested-table-expression.
The FEDERATED keyword restricts the condition to handle only errors that occur at a remote data source.
The condition can be specified as an SQLSTATE value, with a string-constant length of 5. You can optionally specify an SQLCODE value for each specified SQLSTATE value. For portable applications, specify SQLSTATE values as much as possible, because SQLCODE values are generally not portable across platforms and are not part of the SQL standard.
Only certain conditions can be tolerated. Errors that do not allow the rest of the query to be executed cannot be tolerated, and an error is returned for the whole query. The specific-condition-value might specify conditions that cannot actually be tolerated by the database manager, even if a specific SQLSTATE or SQLCODE value is specified, and for these cases, an error is returned.
A query or view containing an error tolerant nested-table-expression is read-only.
The fullselect of an error tolerant nested-table-expression is not optimized using materialized query tables.
Example 1: SELECT t.c1, z.c5
FROM t, TABLE( tf3(t.c2) ) AS z -- t precedes tf3
WHERE t.c3 = z.c4; -- in FROM, so t.c2
-- is known
Example 2: SELECT t.c1, z.c5
FROM t, TABLE( tf4(2 * t.c2) ) AS z -- t precedes tf4
WHERE t.c3 = z.c4; -- in FROM, so t.c2
-- is known
Example 3: SELECT d.deptno, d.deptname,
empinfo.avgsal, empinfo.empcount
FROM department d,
LATERAL (SELECT AVG(e.salary) AS avgsal,
COUNT(*) AS empcount
FROM employee e -- department precedes nested
WHERE e.workdept=d.deptno -- table expression and
) AS empinfo; -- LATERAL is specified,
-- so d.deptno is known
Example 4: SELECT t.c1, z.c5
FROM TABLE( tf6(t.c2) ) AS z, t -- cannot resolve t in t.c2!
WHERE t.c3 = z.c4; -- compare to Example 1 above.
Example 5: SELECT a.c1, b.c5
FROM TABLE( tf7a(b.c2) ) AS a, TABLE( tf7b(a.c6) ) AS b
WHERE a.c3 = b.c4; -- cannot resolve b in b.c2!
Example 6: SELECT d.deptno, d.deptname,
empinfo.avgsal, empinfo.empcount
FROM department d,
(SELECT AVG(e.salary) AS avgsal,
COUNT(*) AS empcount
FROM employee e -- department precedes nested
WHERE e.workdept=d.deptno -- table expression but
) AS empinfo; -- LATERAL is not specified,
-- so d.deptno is unknown