Manipulating Data

The JD Edwards EnterpriseOne database contains object and column names, specific data types and security rules that must be converted or applied so that the data is presented correctly. The specific data types and rules include decimal shifting, Julian date, currency, media object, security, and user-defined codes. In some instances, ODA modifies the SQL SELECT statement, as well as the data, so that it appears correctly within the chosen tool. Once the ODA driver is properly installed and an ODBC data source is established, you can use the functionality of the ODA driver. When a SQL connection is established, the environment of the current connection is stored in the system as the database name. SQLGetInfo can access this value later or it can be used for future connections.

You can use these specific JD Edwards EnterpriseOne features with JD Edwards EnterpriseOne ODA:

Feature	Description
Long Table and Business View Names	Long table and business view names enable you to see a descriptive name when you view an object list. You can use either the descriptive names or the original JD Edwards EnterpriseOne object name in the SELECT statement. Note: This option might not be available for all third-party products (for example, ShowCase STRATEGY products prior to the 2.0 release because the long names contain special characters that are not handled correctly by these tools.
Long Column Names	Long column names enable you to see a descriptive name when viewing any columns list. You can still use either the descriptive names or the original JD Edwards EnterpriseOne column name. For example, you can use either of these statements to retrieve information from the F0101 table: SELECT ABAN8 from the F0101 table. SELECT AddressNumber from the F0101 table.
Julian Date	Julian date modifies all references to Julian date columns to convert the date to an SQL-92 standard date. The JD Edwards EnterpriseOne Julian date is converted to a standard date value that can be used in date calculations. This feature enables you to use duration or other date calculations in both the SELECT (result data), WHERE, and HAVING clauses and the ORDER BY clause. The SQL SELECT statement is modified to before a data calculation to convert the JD Edwards EnterpriseOne Julian date column to a standard date. The modification to the SQL SELECT statement is based on the data source that is being accessed because of driver differences in handling date calculations. If the original column value is zero, the date conversion results in a date value of 1899-12-31. To remove these values, this condition should be added to the WHERE clause in the SELECT statement, where DATECOL is the JD Edwards EnterpriseOne Julian date column: DATECOL <> {d `1899-12-31'}
Decimal Shifting	All references to decimal-shifted columns are modified to shift the decimal point to cause the result data to be correct. This feature enables SQL statements that contain complex expressions, aggregates, and filtering to run and return accurate results. The SQL SELECT statement is modified to divide the column by the appropriate number of decimal places so that the data is returned correctly and to make compare operators work for filtering.
Currency	Currency columns are limited to single-column references in the selected columns list. Returned data is converted using the standard JD Edwards EnterpriseOne currency conversion routines. All other references to the currency column in the SQL statement are passed through to the native driver. You must understand how the currency column is used to make effective use of filtering. Before selected columns are returned, the JD Edwards EnterpriseOne Open Data Access driver converts any currency columns to the correct value. Currency columns used in the WHERE or HAVING clause are processed based on the non-converted currency value. Currency columns in the GROUP BY or ORDER BY clause are grouped and sorted by the non-converted currency value.
Media Object	The Media object column, TXVC, in the F00165 table storage is limited to single-column references in the selected columns list. ODA returns media data in plain text or rich text format (RTF) and truncates other binary data, such as an image. The size limitation of the text or RTF is 30,000 characters, and text will be truncated when it reaches this limitation.
Column Security	When column security is active, any reference to restricted columns causes an error to be returned when the SELECT statement is examined, including the use of * (asterisk-selecting all columns) in the select clause, as defined by the SQL-92 standards. You will receive an error if you are not authorized for all of the columns in the table.
Row Security	When row security is active, the statement is modified to include the appropriate WHERE clause for filtering secured rows. You will only see rows that you are authorized to access along with getting accurate results using aggregate functions-for example, SUM or AVG.
User Defined Codes	When user-defined codes (UDCs) are enabled, you see the associated description instead of the internal code when the column data is returned. This processing affects only the returned data and has no effect on the other parts of the Select statement (for example, Where, Order By and so on). This is an optional setting that can be configured when you set up the driver. Before the UDC is returned to you, the JD Edwards EnterpriseOne Open Data Access driver converts the code to the associated description. The UDC columns used in the WHERE or HAVING clause are selected based on the non-converted code and the UDC columns referenced in the GROUP BY and ORDER BY clause are grouped and sorted by the non-converted code.