Special Cases
Obtaining a Value when No Govern ID is Present
SQL Queries are used for Calculated Fields, Formulas, and Logical Expressions. In the SQL Query Editor, users are provided with a list of Govern ID’s that are used as keywords in queries. A Keyword type was introduced in Release 4.7, 5.1, and Release 6.0. When building a query, instead of using Govern ID’s, the attribute name in a Business Entity (BE) can be used to read its value.
An advantage of the attribute keyword search syntax is that it can be used to obtain attribute values from Business Entities (BE’s) that do not use any Govern ID’s. As long as the attribute is specified explicitly, its value can be obtained from the associated BE.
Read More... EXAMPLE SELECT (LTRIM(CAST((CASE WHEN NA_NAMES.FREE_LINE_1 IS NOT NULL Prior to the Entity/Attribute keyword, the FREE_LINES_X values could not be obtained in the traditional manner because it is not a Govern ID. With the keyword we are able to compare the value of FREE_LINE_X with our NA_ID. 100-Queries-(Special Cases)
In the following example, we want to compare an NA_ID value in the NA_NAMES table with the values FREE_LINES_X, where X is a value of 1 to 6; these values are stored in the NA_LK_TO_NAME Business Entity.
In the query sample, using the SELECT and CASE statement, the values contained in FREE_LINE_1, FREE_LINE_2, FREE_LINE_3, FREE_LINE_4, FREE_LINE_5, and FREE_LINE_6 can be retrieved.
LTRIM – Removes any leading spaces; CAST – Casts the value as a variable character typeQuery Fragment
THEN NA_NAMES.FREE_LINE_1
ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_2 IS NOT NULL
THEN CHAR(13) || CHAR(10) || NA_NAMES.FREE_LINE_2
ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_3 IS NOT NULL
THEN CHAR(13) ||CHAR(10) || NA_NAMES.FREE_LINE_3
ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_4 IS NOT NULL
THEN CHAR(13) ||CHAR(10) ||
NA_NAMES.FREE_LINE_4 ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_5 IS NOT NULL
THEN CHAR(13) ||CHAR(10) || NA_NAMES.FREE_LINE_5
ELSE ” END)
AS VARCHAR(80)) ||
CAST((CASE WHEN NA_NAMES.FREE_LINE_6 IS NOT NULL
THEN CHAR(13) ||CHAR(10) || NA_NAMES.FREE_LINE_6
ELSE ” END)
AS VARCHAR(80)))) NAME_DESC
FROM NA_NAMES
WHERE NA_NAMES.NA_ID=attr@NA_LK_TO_NAME.LINK_NA_ID
To do a comparison with a SQL query, the syntax for our keyword (attr@EntityName.AttributeName) will specify the code from the entity and
the name of the required attribute.
In the last line of the query sample, the value in the NA_ID is compared to the value in the LINK_NA_ID in the NA_LK_TO_NAME Business Entity.
NA_NAMES.NA_ID=attr@NA_LK_TO_NAME.LINK_NA_ID
…where…
EntityName = NA_LK_TO_NAME and AttributeName = LINK_NA_ID
NOTE: Currently the entity and attribute names are entered manually, therefore caution should used when typing.
Related Topics
(0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.
Loading...