[
https://issues.apache.org/jira/browse/TRAFODION-3034?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16635027#comment-16635027
]
ASF GitHub Bot commented on TRAFODION-3034:
-------------------------------------------
Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1688#discussion_r221489512
--- Diff: core/sql/bin/SqlciErrors.txt ---
@@ -1515,6 +1515,9 @@ $1~String1 --------------------------------
8034 ZZZZZ 99999 ADVANCED MAJOR DBADMIN Column $0~String0 of object
$1~string1 does not have a default clause but it is missing in database. This
indicates inconsistent data.
8035 ZZZZZ 99999 ADVANCED MAJOR DBADMIN Truncation of hive table failed.
$0~String0
8036 ZZZZZ 99999 ADVANCED MINOR LOGONLY Error while creating the error
logging file or logging the error row to file $0~String0: Details :$1~String1
+8037 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Loop detected in connect by
execution.
--- End diff --
In these three messages, it would help if we could give information
specific to the instance when the error occurred. Maybe value of the prior
column(s) as the error occurred. For the case of recursion exceeding a certain
length or memory being exhausted, more details what level of recursion has been
reached or how much memory has been consumed (if that information is readily
available) would be helpful. This is an advisory suggestion, and by no means
something to be addressed soon.
I would have expected some binder errors for statements where hierarchical
constructs are not supported. It is possible though unlikely that all are
caught in the parser. For example what happens if a hierarchical function or
pseudocolumn is used a regular (non-hierarchical query)
> support Oracle 'Start with connect by' feature
> ----------------------------------------------
>
> Key: TRAFODION-3034
> URL: https://issues.apache.org/jira/browse/TRAFODION-3034
> Project: Apache Trafodion
> Issue Type: New Feature
> Reporter: liu ming
> Assignee: liu ming
> Priority: Major
>
> Oracle support hierarchy search , or recursive query. Syntax is 'connect by,
> start with'
>
> If a table contains hierarchical data, then you can select rows in a
> hierarchical order using the hierarchical query clause (START WITH
> condition1) CONNECT BY condition2
> The START WITH clause is optional and specifies the rows athat are the
> root(s) of the hierarchical query. If you omit this clause, then Oracle uses
> all rows in the table as root rows. The START WITH condition can contain a
> subquery, but it cannot contain a scalar subquery expression.
> The CONNECT BY clause specifies the relationship between parent rows and
> child rows of the hierarchy. The connect_by_condition can be any condition,
> however, it must use the PRIOR operator to refer to the parent row.
> Restriction on the CONNECT BY clause: The connect_by_condition cannot contain
> a regular subquery or a scalar subquery expression.
> The PRIOR operator to refer to the parent row can be used as following:
> Sample query on the employees table:
> SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY
> PRIOR employee_id = manager_id;
> shows employee_id, last_name, manager_id and level in the tree for the
> employee hierarchy.
> In Oracle 9i a new feature for hierarchical queries is added:
> sys_connect_by_path It returns the path of a column value from root to node,
> with column values separated by char for each row returned by CONNECT BY
> condition. Both column and char can be any of the datatypes CHAR, VARCHAR2,
> NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in
> the same character set as column.
> Examples
> The following example returns the path of employee names from employee
> Kochhar to all employees of Kochhar (and their employees):
> SELECT LPAD(' ', 2*level-1)|| SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM
> employees CONNECT BY PRIOR employee_id = manager_id;
> In Oracle 10 3 new features for hierarchical queries are added:
> h3. connect_by_root
> CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical
> queries. When you qualify a column with this operator, Oracle returns the
> column value using data from the root row. This operator extends the
> functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.
> Restriction on CONNECT_BY_ROOT: You cannot specify this operator in the START
> WITH condition or the CONNECT BY condition. Example query:
> SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager", LEVEL-1
> "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE
> LEVEL > 1 and department_id = 110 CONNECT BY PRIOR employee_id = manager_id;
> h3. connect_by_isleaf
> The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of
> the tree defined by the CONNECT BY condition. Otherwise it returns 0. This
> information indicates whether a given row can be further expanded to show
> more of the hierarchy. Example
> SELECT employee_id, last_name, manager_id, connect_by_isleaf "IsLeaf" FROM
> employees START WITH last_name = 'King' CONNECT BY PRIOR employee_id =
> manager_id;
> h3. connect_by_iscycle
> The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child
> which is also its ancestor. Otherwise it returns 0.
> You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE
> parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the
> results of a query that would otherwise fail because of a CONNECT BY loop in
> the data.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
