In Oracle, if I have a table defined as … CREATE TABLE taxonomy ( key NUMBER(11) NOT NULL CONSTRAINT taxPkey PRIMARY KEY, value VARCHAR2(255), taxHier NUMBER(11) ); ALTER TABLE taxonomy ADD ...

Test #1: I have a user-defined function and a CONNECT BY LEVEL query: with function custom_function(p_id in number) return number is begin return p_id; end; select custom_function(level) from...

I have a Oracle query with a NOCYCLE clause which I have to translate to Postgres: SELECT FG_ID,CONNECT_BY_ROOT FG_ID as Parent_ID FROM FG t START WITH t.Parent_filter_group_id is null CONNECT B...

I've got this Oracle code structure I'm trying to convert to SQL Server 2008 (Note: I have used generic names, enclosed column names and table names within square brackets '[]', and done some forma...

I tried to find some informations about connect by "engine". I found this post: Confusion with Oracle CONNECT BY User krokodilko answered and says: The analyze of the last query: select level fr...

I would like to get all countries separated by N (1,2,3,4 ...) borders from a specified country. N should also be specified. For example I have the table "borders" and "country": border | ne...

Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle? If not, then how can we create a column similar to "LEVEL"?

Following query shows that select * combined with connect by and left join doesn't return all columns, but only columns used in these conditions. This behavior was useful for me, given that select ...

Using CONNECT BY LEVEL seems to return too many rows when performed on a table. What is the logic behind what's happening? Assuming the following table: create table a ( id number ); insert into...

Relationship model is 1 3 \ / \ 2 4 \ 7 5 8 \ / / 6 9 Table is : select 2 child, 1 father from dual union all select 2 child, 3 father from dual union all select 4 child, 3 father from d...

In simplified terms, I'm trying to calculate the percentage of the root of a tree owned by its parents, further up the tree. How can I do this in SQL alone? Here's my (sample) schema. Please note t...

I have a query requirement from ----. Trying to solve it with CONNECT BY, but can't seem to get the results I need. Table (simplified): create table CSS.USER_DESC ( USER_ID VARCHAR2(30) not n...

Oracle ships with a very handy feature. You can create hierarchical queries (recursive behaviour) using the following clause: CONNECT BY [NOCYCLE] {condition [AND condition...]} [START WITH condit...

Given a simple (id, description) table t1, such as id description -- ----------- 1 Alice 2 Bob 3 Carol 4 David 5 Erica 6 Fred And a parent-child relationship table t2, such as parent child ----...