Skip to main content
insightsoftware Documentation insightsoftware Documentation
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

SQL Code Standards in NoetixViews Workbench

When adding a custom view through NoetixViews Workbench, make sure that the SQL code meets the following requirements:

  • The view labels can contain a maximum of 30 characters; however, the following points need to be considered when providing a view label:

    • For a simple view, the view label cannot exceed 22 characters for the following reasons:

      • When you create a custom view, a prefix is automatically added to the view label. The prefix contains the abbreviated name of the Oracle E-Business Suite module corresponding to the view followed by an underscore. A maximum of five characters are used for the abbreviated name. For example, a view that is generated for Oracle General Ledger will contain the prefix "GL , as in

      • When you add a rowid column-based master key or join to a view, a column with the prefix "Z$" followed by a view label will be added to the view. For example, Z$GLG0 All Balances is the rowid column-based master key column for the GLG0 All Balances view, and Z$GLG0 Chart Of Accounts is the join column in GLG0 All Balances to the GLG0 Chart Of Accounts view. The resulting column label needs to be within the limit of 30 characters for column labels.

    • For a list of values (LoV) view, the view label cannot exceed 20 characters because "LOV is automatically added to the view label after the abbreviated module name prefix, as in

    • For a base view, the view label cannot exceed 19 characters because a prefix for the abbreviated module name and the suffix " Base" are automatically added to the view label, as in AP Bank Branches Base.

  • For column labels, the following guidelines are applicable:

    • For simple, constant, and expression type columns, the label can contain a maximum of 30 characters, must begin with a letter, and can contain only letters, numbers, and underscores. If you want to generate a search-by (A$) column for this column, the length of the column label must notexceed 28 characters. The column label cannot be the same as that of an existing simple, constant, or expression type column. However, the label can match with that of an existing column of any type except a simple, a constant, or an expression type with different capitalization.

    • For key flexfield and descriptive flexfield columns, the label cannot exceed eight characters.

    • For lookup columns, the label can contain a maximum of 30 characters, must begin with a letter, and can contain only letters, numbers, and underscores. If you want to generate an A$ column for this column, the maximum length must not exceed 28 characters.

  • Table aliases must be unique and cannot exceed five characters.

  • In SELECT statements, columns need to be individually listed and separated by commas.

  • The FROM clause must contain only table names or view names with aliases.

  • The WHERE clause must contain only subqueries, functions, case statements, decode statements, and inner joins.

  • The single-line or multi-line comments must be enclosed within "/*" and "*/" and not within delimiters, such as "--".

  • For GROUP BY clauses, consider the following:

    • A GROUP BY clause cannot include an expression unless the expression is specified as a column and associated with a column alias.

    • The expression that is part of the GROUP BY clause must also be added to the SELECT statement.

    • In the GROUP BY clause, the order of the group by columns mentioned in the SQL code is not preserved; the columns are arranged and saved based on the alphabetical order of the column labels.

  • Multiple queries are supported.

  • UNION ALL, UNION, MINUS, and INTERSECT set operators are allowed.

  • When adding a base view to your view, make sure that the base view belongs to the same Oracle E-Business Suite application as the view that you are adding to.

  • Column expressions must contain only subqueries, functions, calculations, case statements, decode statements, literals, and constants.

  • When providing a column expression, you must include table aliases with column labels. If you do not include the table alias, and if multiple tables contains the same column label, Stage 4 may fail with an error.

  • When providing the NULL value for a column expression, you must use TO NUMBER(NULL), TO CHAR(NULL), or TO DATE(NULL) as applicable to avoid any data type-related errors.

In the SQL code for a custom view, the code must not include the following elements:

  • SQL statements of the ANSI SQL 92 standards

  • TOP and DISTINCT clauses in SELECT statements

  • FROM clauses containing the JOIN keyword

  • ORDER BY and HAVING clauses

  • Spaces and characters in column labels that are not supported by the Oracle Database

Unsupported Column Types in NoetixViews Workbench

Published:

SQL Code Standards in NoetixViews Workbench

When adding a custom view through NoetixViews Workbench, make sure that the SQL code meets the following requirements:

  • The view labels can contain a maximum of 30 characters; however, the following points need to be considered when providing a view label:

    • For a simple view, the view label cannot exceed 22 characters for the following reasons:

      • When you create a custom view, a prefix is automatically added to the view label. The prefix contains the abbreviated name of the Oracle E-Business Suite module corresponding to the view followed by an underscore. A maximum of five characters are used for the abbreviated name. For example, a view that is generated for Oracle General Ledger will contain the prefix "GL , as in

      • When you add a rowid column-based master key or join to a view, a column with the prefix "Z$" followed by a view label will be added to the view. For example, Z$GLG0 All Balances is the rowid column-based master key column for the GLG0 All Balances view, and Z$GLG0 Chart Of Accounts is the join column in GLG0 All Balances to the GLG0 Chart Of Accounts view. The resulting column label needs to be within the limit of 30 characters for column labels.

    • For a list of values (LoV) view, the view label cannot exceed 20 characters because "LOV is automatically added to the view label after the abbreviated module name prefix, as in

    • For a base view, the view label cannot exceed 19 characters because a prefix for the abbreviated module name and the suffix " Base" are automatically added to the view label, as in AP Bank Branches Base.

  • For column labels, the following guidelines are applicable:

    • For simple, constant, and expression type columns, the label can contain a maximum of 30 characters, must begin with a letter, and can contain only letters, numbers, and underscores. If you want to generate a search-by (A$) column for this column, the length of the column label must notexceed 28 characters. The column label cannot be the same as that of an existing simple, constant, or expression type column. However, the label can match with that of an existing column of any type except a simple, a constant, or an expression type with different capitalization.

    • For key flexfield and descriptive flexfield columns, the label cannot exceed eight characters.

    • For lookup columns, the label can contain a maximum of 30 characters, must begin with a letter, and can contain only letters, numbers, and underscores. If you want to generate an A$ column for this column, the maximum length must not exceed 28 characters.

  • Table aliases must be unique and cannot exceed five characters.

  • In SELECT statements, columns need to be individually listed and separated by commas.

  • The FROM clause must contain only table names or view names with aliases.

  • The WHERE clause must contain only subqueries, functions, case statements, decode statements, and inner joins.

  • The single-line or multi-line comments must be enclosed within "/*" and "*/" and not within delimiters, such as "--".

  • For GROUP BY clauses, consider the following:

    • A GROUP BY clause cannot include an expression unless the expression is specified as a column and associated with a column alias.

    • The expression that is part of the GROUP BY clause must also be added to the SELECT statement.

    • In the GROUP BY clause, the order of the group by columns mentioned in the SQL code is not preserved; the columns are arranged and saved based on the alphabetical order of the column labels.

  • Multiple queries are supported.

  • UNION ALL, UNION, MINUS, and INTERSECT set operators are allowed.

  • When adding a base view to your view, make sure that the base view belongs to the same Oracle E-Business Suite application as the view that you are adding to.

  • Column expressions must contain only subqueries, functions, calculations, case statements, decode statements, literals, and constants.

  • When providing a column expression, you must include table aliases with column labels. If you do not include the table alias, and if multiple tables contains the same column label, Stage 4 may fail with an error.

  • When providing the NULL value for a column expression, you must use TO NUMBER(NULL), TO CHAR(NULL), or TO DATE(NULL) as applicable to avoid any data type-related errors.

In the SQL code for a custom view, the code must not include the following elements:

  • SQL statements of the ANSI SQL 92 standards

  • TOP and DISTINCT clauses in SELECT statements

  • FROM clauses containing the JOIN keyword

  • ORDER BY and HAVING clauses

  • Spaces and characters in column labels that are not supported by the Oracle Database

Unsupported Column Types in NoetixViews Workbench

For an optimal Community experience, Please view on Desktop
Powered by Zendesk