The SQL block has the following properties:
Property
Description
Data source
The source
from which the report obtains
data.
To
select or change the
data source, click Change. This opens the SQL
Data Source dialog box,
from which you can
select from a list of
data sources (such as ODBC, JDBC, and Oracle
database connections) configured in the apf.xml file for use with APF.
You can use the All users option to set an SQL username/password for all users on the
system (allowing services that are run as the
SYSTEM user to access them).
Important Before using the All users option, ensure that you have administrative privilege on the
system to write to the registry, HKEY_LOCAL_MACHINE\SOFTWARE\Applied Materials\APF Common\ODBC.
Note If necessary, you can log in to the
database by clicking Login in the SQL
Data Source dialog box.
Tip You can also configure SQL
database access for all APF clients using the SQL Password Administrator utility, accessible
from the Windows Start menu in Programs > APF Vx.x.x > SQL Password Admin. This allows you to easily up
date database credentials
from a single source (rather than needing to up
date credentials in individual blocks).
Folder
The
database table or folder
from which you want to extract attribute values. The tables are displayed in a combo tree structure.
Notes
If you are using an ODBC connection, accessing a Tera
data database source for the first time might log some errors in the ODBC Server log file. This is because the schemas are accessed along with the table
data, and these two could not be distinguished. The error is due to the schemas not containing
data, and does not affect the
data in the folders. The errors do not appear on subsequent
selections.
If you do not have the required permission to access a table directly, the available columns of the
selected table are not displayed in the Columns list. In this case, you must
select the schema.table (that is, qualify the table with its schema).
Attributes
Lists all attributes in the
selected folder.
Columns
Lists the attributes whose values you want to extract for use in the report or rule. Drag attributes here
from the Attributes list.
The SQL block automatically creates an SQL query to extract the attribute values that you
select.
Note If you want to use duplicated column names in the SQL query, ensure that you use alias names for the duplicated columns.
Bind Variables
Click this option to create, up
date, or delete bind variables. Using bind variables can improve
database performance, by limiting the number of hard parses performed by the
database during rule or report execution. A hard parse occurs when the
database must allocate CPU and memory to execute a unique SQL query that has not previously been executed. Using bind variables allows you to write a common SQL query that differs in execution only by its variable values; in this case, the
database executes the query
from its shared pool (cache), with improved performance.
Important Bind variables are index-based (rather than name-based) and, therefore, the order in which the Bind Variables appear in the Add/Remove Bind Variables window must match the order they appear in the Manual SQL query value. Also, any bind variable defined must be referenced in the Manual SQL expression. That is, you cannot define two bind variables and use only one in the expression.
The properties in the Add/Remove Bind Variables window are:
Bind Variables - The list of bind variables.
Name – The name of the bind variable.
Type – The APF
data type of the variable.
Value – The value you want to assign to the bind variable.
A sample query using bind variables for a
database connection that is using an OCI driver is shown below:
SELECT Name
from Equipment where EqpType = :EqType
where EqType is the name of the bind variable.
Note To use bind variables, you must enable the Manual SQL option in the SQL block's properties window, and then define an SQL query that uses the appropriate variables.
To edit a bind variable,
select the variable
from the Bind Variables list, modify the values in the Edit group box and then click Up
date.
Notes
Bind variables are not supported when using ODBC and JDBC connections.
When using
date type bind variables, specify the
date in ISO format. For example, in US based regional setting, specify
date in the MM-DD-YYYY format.
Manual SQL
Select this option to customize the block's default SQL query or define your own query; clear this option to use a
system-defined SQL query based on the properties you have defined for the SQL block.
Note If you want to use bind variables to improve query performance (as described in the property description above), you must
select Manual SQL, and then define a query that includes the appropriate variables. (In this case, the order in which bind variables appear in the SQL query must match the order they are defined in the Add/Remove Bind Variables window).
When this check box is
selected, you can choose attributes
from a different table and later change them in the manual query. The attributes listed in the Columns list remain unchanged.
Warning Defining a manual query that
selects all columns in a table (for example,
SELECT *
FROM MYTABLE) can significantly slow the runtime performance of the report or rule (depending on the size of the target table). To improve performance, define a
SELECT statement that
selects specific columns (for example,
SELECT COLUMN1,COLUMN2
FROM MYTABLE).
Notes
You can use
system- or user-defined constants to represent field values. For more information, see Using constants in SQL queries.
When defining manual SQL queries, functions must be represented by an alias. For example, the following SQL query creates a table column named LotNoCnt that represents the result of the Count(LOT_NO) function call:
SELECT Count (LOT_NO) LotNoCnt, ...
FROM...WHERE...
Escape SQL
Allows you to enable or disable the use of escape characters to represent table or column names when running the SQL query. By default this option is
selected.
When you
select the Escape SQL option, you must enter the table and column names in the same case as they are created in the Oracle
database. For example, if a table named LOT_TABLE contains the columns ID and QTY in the Oracle
database, and if you have
selected the Escape SQL option, the SQL query should be of the form:
SELECT ID,QTY
FROM LOT_TABLE;
The following SQL query would not work, because the table and column names are specified in lowercase.
SELECT id,qty
FROM lot_table;
Note If you use multiple tables in the SQL query, do not
select the Escape SQL check box. In general, when using a manual SQL query, it is recommended to clear the Escape SQL checkbox.
No optimize
Select this option if you do not want the expression in the next block to be pushed to the SQL query when using the Manual SQL option. By default, this option is cleared.
SQL Query
Displays the SQL query that will be executed. When you
select the Manual SQL option, you can type a custom SQL query in this field.
To add in-line comments to your query, either use the /* and */ combination or double hyphen (--). The following example shows both the usages.
SELECT lot, /*
select the name */
oper, --
select the operation
qtyout
FROM ALLFORMAT
Notes
If you use downstream Filter, Function, or Compress blocks to operate on
data returned
from an SQL block, in some cases, the compiler performs automatic optimization of your SQL query to improve performance when running the report or rule. For more information, see Understanding automatic SQL optimization.
If you use a manual
SELECT statement such as "
select *
from <table_name>" in the SQL block query, and later change the table schema, you must recompile the report.
Some SQL functions, including SUM() and AVG(), return an incorrect
data type when used with OCI or JDBC
data sources. For details, click here.
A problem in the OCI and JDBC drivers results in return values of the wrong
data type for some SQL functions including SUM() and AVG(). The following expression returns an INTEGER instead of the expected REAL value:
SELECT SUM(ints)/4 AS RESULT
FROM MYTABLE
A workaround is available by embedding the problematic function within a CAST expression to convert the result to the desired type, as follows:
SELECT CAST(SUM(ints)/4 AS REAL) AS RESULT
FROM MYTABLE
This workaround is valid for Oracle 10g without any additional steps. However, using Oracle 9.2, 9.2.0.6, or 9i, this workaround requires that you run the following commands in SQLPLUS to configure a
database-level event:
sql>alter
system set event='10507 trace name context forever,level 1' scope=spfile:
sql>shutdown immediate;
sql>startup
Once the
database has been restarted, the CAST function will work as needed in Oracle 9.2, 9.2.0.6, or 9i.
When using an SQL block to connect to an Oracle
database containing a synonym for a defined view (for example, MYVIEW_SYN), the synonym does not appear in the block's properties.
The SQL block does not support using case-sensitive table names when accessing columns though a
database link that points to
another database.
If you include native functions in your SQL statements, the return
data type (when using an OCI connection) is determined by Oracle, and a similar
data type is created in APF. If you want to use a specific
data type for a column, use the native Oracle function, CAST(column, oracle_
data_type).
The SQL block does not support comments in the SQL statement using the "—" format; instead enclose the comments between /* and */. The incorrect and correct usages are given below.
Incorrect usage:
SELECT TABLE1.NAME AS "NEW_NAME" --comments
Correct usage:
SELECT TABLE1.NAME AS "NEW_NAME" /*comments*/
Important
The keyword OVER is not supported in the SQL statement inside an SQL block. For example,
SELECT COL1, COL2 AVG(COL2) OVER (PARTITION BY COL3) AS RES
FROM TABLE1.
If you use Oracle versions prior to 10g and include multiple APF user-defined constants in the SQL query, the call to JDBC API fails. This is because, an SQL query like the one given below:
SELECT *
FROM EMPLOYEE WHERE NAME=$CONSTANT1 AND AGE=$CONSTANT2;
is converted to a internal format when making an OCI or JNI native call. The JNI native calls do not support this internal format if the Oracle version is less than 10g and may return the following error, because the integer type AGE is passed internally as string:
ORA-01722: invalid number
However, you can include multiple user-defined constants in the SQL query when using an OCI connection.
翻译
本文介绍了一种复杂的SQL查询方法，用于从特定组织中获取物料需求数据，并计算使用率。通过连接不同表来筛选符合特定条件的需求记录，并对这些记录进行分组统计。
扫一扫
4056
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
