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 update
database credentials from a single source
(ra
ther than needing to update 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 Teradata
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, update, 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
(ra
ther 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 Update.
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, ei
ther 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 ano
ther
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.
翻译
扫一扫
1636
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
