In Strategy Enterprise Manager 9.x, 'SQL Clause Type' is an indicator attribute that is used to determine which SQL clause (SELECT, WHERE, GROUP BY, and so on), a particular column was used in,during a report execution. This attribute is present in the out-of-the-box report, '44. Warehouse Tables Accessed'. The Lookup table for this attribute is IS_SQL_CLAUSE_TYPE, which contains the following options:
Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY | Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY |
Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY | Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY |
Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY | Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY |
Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY | Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY |
Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY | Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY |
Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY | Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY |
Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY | Samsung Nexus S 4G
Sony Tablet SSony Xperia Arc International versionSony Xperia S International versionToshiba ThriveCertified Android Operating SystemsAndroid OS 4.xEvaluate the test metric over all Region elements, producing a filtered Region set.Collect Region and Year elements separately.Collect Year elements.Evaluate the test metric over the cross join of both dimensions (unfiltered).Select template metrics against the Year elements cross joined with the pre-filtered set of Region elements.Select template metrics against the post-filtered cross join set.NameDefault port numberDescriptionReceiver port20009The Logging Clients collect messages from system components on this port.Relay port20020The Logging Clients connect to the Logging Server on this port to relay messages.Consumer port20030The Logging Server relays messages to the Logging Consumer, System Message Viewer and Narrowcast API clients on this port.Buffer port20040The Narrowcast Administration Console connects to the Logging Server to read buffered information on this port.MemSQL 2.6 32-bit MySQL ODBC Driver 5.2.5 CertifiedValue (IS_SQL_CLAUSE_ID)Description (IS_SQL_CLAUSE_DESC)1SELECT2SELECT GROUP BY4SELECT AGGREGATE8FROM16WHERE17ORDER BY |
These values for report executions are logged in the
SQLCLAUSETYPEID
field of
IS_REP_COL_STATS
table and subsequently loaded into the
SQL_CLAUSE_TYPE_ID
field of
IS_REP_COL_FACT
table.
Following is a detailed description of these options:
- 1 (
SELECT
) means that column appears in Select clause but is neither aggregated nor does it appear in Group By clause. - 2 (
SELECT GROUP BY
) means that column appears in Select clause as well as in Group By clause. - 4 (
SELECT AGGREGATE
) means that column appears in Select clause and is aggregated. - 8 (
FROM
) means that column appears in From clause. - 16 (
WHERE
) means that column appears in Where clause. - 17 (
ORDER BY
) means that column appears in Order By clause.
Consider the following example SQL statement:
select a12.COUNTRY_ID COUNTRY_ID,max(a13.COUNTRY_NAME) COUNTRY_NAME,sum(a11.TOT_DOLLAR_SALES) WJXBFS1from STATE_SUBCATEG_REGION_SLS a11join LU_REGION a12on (a11.REGION_ID = a12.REGION_ID)join LU_COUNTRY a13on (a12.COUNTRY_ID = a13.COUNTRY_ID)group by a12.COUNTRY_ID
For this example, the values recorded in the
SQLCLAUSETYPEID
field of
IS_REP_COL_STATS
table (or the
SQL_CLAUSE_TYPE_ID
field in corresponding Strategy Enterprise Manager 9.x table
IS_REP_COL_FACT
) are as follows:
COUNTRY_ID
is logged three times - twice with value 16 and once with value 2. This means it appears twice in the Where clause and once in Select and Group By clauses, as confirmed by the above SQL.COUNTRY_NAME
and TOT_DOLLAR_SALES
are each logged once with value 4. This means that these columns appear in Select clause and are aggregated, as confirmed by the above SQL.REGION_ID
is logged twice with value 16. There are two occurrences in Where clause, as confirmed by the above SQL.
Consider a second example SQL statement:
select a11.COUNTRY_ID COUNTRY_ID,a11.COUNTRY_NAME COUNTRY_NAMEfrom LU_COUNTRY a11
For this second example, the values recorded in the
SQLCLAUSETYPEID
field of
IS_REP_COL_STATS
table (or the
SQL_CLAUSE_TYPE_ID
field in corresponding Strategy Enterprise Manager 8.x table
IS_REP_COL_FACT
) are as follows:
- Both
COUNTRY_ID
and COUNTRY_NAME
are logged once, each with value of 1 (Select
clause but not in Group By
clause and not aggregated.)
