Query Builder BOM tree with Oracle8 level of implementation: Flow square Home: http: //liulee.yeah.net
I recently received a question about the hierarchical query of Oracle, I found a very useful query clause in Oracle 8i: SELECT Level, RowId, ... from ... Where ... start with ... connect by EXPRESSION; grammatical description: ***************************************************** ******** START with ... Connect By (this is a word, can not be opened) grammar details, please refer to Oracle Release 8.0.5 Documentation Library SELECT Help File Location Doc / Database .804 / a58225 / ch4l.htm Brief Description: START with ... Connect By: Building a hierarchical query ("hierarchical queries") Start with ... clause: User root record Conditions CONNECT BY clause: used for children Record the connection method of the combiner record. Is a specific conditional expression, one must be one: prior expr Comparaison_operator expr company_operator prior expr
If you define a Start with ... Connect By clause, Level returns the title number of the current record. *********************************************************** ***
This syntax can get a hierarchical result set by defining a CURSOR or view, which is very useful, especially for the generation of the EBOM tree in the production company, will significantly change the implementation efficiency of BOM tree generation, so I will The content of the grammar is analyzed:
Give this data: DOC_PR DOC_CH TITLE -------------------------------------- ----------- DOC A Class A A2 A-2 A A3 A-3 A A1 A-1 A1 A11 A1-1 DOC B B B B1 B-1 B B2 B-2 B B3 B-3 B3 B31 B-3-1 DOC C C C C1 Test C1 Define a cursorDeclare curTree Cursor For select level, doc_ch, title, rowid from doc_tree start with doc_pr = 'DOC' connect by doc_pr = Prior doc_ch; return result sets as follows: LEVEL DOC_CH TITLE ROWID -------- --- ------------------------------------------------- ------------------------ 1 A AAAAI AADAAAAAAAAAA 2 A2 A-2 AAAAI AADAAAAAM 2 A3 A-3 AAAAI
AADAAAAyNAAN 2 A1 A1 AAAAi AADAAAAyNAAP 3 A11 A1-1 AAAAi AADAAAAyNAAS Class 1 B B AAAAi AADAAAAyNAAF 2 B1 B1 AAAAi AADAAAAyNAAO 2 B2 B2 AAAAi AADAAAAyNAAQ 2 B3 B3 AAAAi AADAAAAyNAAR 3 B31 B-3-1 AAAAI AADAAAAAAAAAAAAAAAAAG 2 C1 Test C1 AAAAI AADAAAAAAAAC This syntax Oracle internal logic I analyze as follows: Select Level, DOC_CH, TITLE, ROWID from DOC_TREE to start with DOC_PR = "DOC" After connecting the first result set with DOC_PR = last result, as_start is automatically converted to DOC_CH of the current row, then executes SELECT Level, DOC_CH, TITLE, RO Wid from doc_tree where doc_pr = prior doc_ch This loop this level result set, then start the process of the next result set. This is the internal logic, and the return to the user has such a result set with the result set, which is very convenient for establishing TreeView, as long as all records can be easily implemented.
In Sybase, I don't know if there is such a syntax in MS SQL Server, I am not too clear, if you know, please tell me.
Now I am in the MS SQL Server is this: add a field level, not a pseudo column. The principle of implementation is consistent with the above.
