[ https://issues.apache.org/jira/browse/TRAFODION-3034?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16635028#comment-16635028 ]
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_r221489667 --- Diff: core/sql/comexe/ComTdbExeUtil.cpp --- @@ -3028,3 +3028,59 @@ Lng32 ComTdbExeUtilLobInfo::unpack(void * base, void * reallocator) return -1; return ComTdbExeUtil::unpack(base, reallocator); } + +ComTdbExeUtilConnectby::ComTdbExeUtilConnectby(char * query, + ULng32 querylen, + Int16 querycharset, + char * tableName, + char * stmtName, + ex_expr * input_expr, + ULng32 input_rowlen, + ex_expr * output_expr, + ULng32 output_rowlen, + ex_expr * scan_expr, + ex_cri_desc * work_cri_desc, + const unsigned short work_atp_index, + Lng32 colDescSize, + Lng32 outputRowSize, + ex_cri_desc * given_cri_desc, + ex_cri_desc * returned_cri_desc, + queue_index down, + queue_index up, + Lng32 num_buffers, + ULng32 buffer_size, + ExCriDescPtr workCriDesc + ) + : ComTdbExeUtil(ComTdbExeUtil::CONNECT_BY_, + query, querylen, querycharset, + tableName, strlen(tableName), + input_expr, input_rowlen, + output_expr, output_rowlen, + scan_expr, + work_cri_desc, work_atp_index, + given_cri_desc, returned_cri_desc, + down, up, + num_buffers, buffer_size), + flags_(0), + myWorkCriDesc_(workCriDesc), + tupleLen_(outputRowSize) +{ + setNodeType(ComTdb::ex_CONNECT_BY); + connTableName_ = tableName; + maxDeep_ = 200; //by default, max deep of a tree --- End diff -- These two literals could be consts to improve readability. It looks like there are cqds defined to make these configurable, which is excellent. > 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)