Home » SQL & PL/SQL » SQL & PL/SQL » Writing a Hierarchical Query without using the connect by prior (Oracle)
| Writing a Hierarchical Query without using the connect by prior [message #634122] |
Wed, 04 March 2015 12:04  |
 |
sachinj
Messages: 13
Registered: February 2012
Location: India
|
Junior Member |
|
|
--Table Creation Script
CREATE TABLE USERS
(
USERID INTEGER NOT NULL,
USERNAME VARCHAR2(255 BYTE) NOT NULL,
FULLNAME VARCHAR2(255 BYTE) NOT NULL,
USER_GROUP INTEGER NOT NULL,
USER_TYPE INTEGER,
IS_DELETED CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
IS_PROFILE_MANAGER CHAR(1 BYTE) DEFAULT 'N',
LAST_LOGIN_DATE DATE,
LOCKED_OUT_INDICATOR INTEGER DEFAULT 0,
LOCK_OUT_REASON VARCHAR2(255 BYTE) DEFAULT ' ',
NUM_FAILED_ATTEMPTS INTEGER DEFAULT 0 NOT NULL,
DISABLE_PASSWORD_EXPIRY CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
FORCE_PASSWORD_CHANGE CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
LAST_PASSWORD_CHANGE DATE,
ENCRYPTED_PASSWORD VARCHAR2(255 BYTE),
USER_DISTINGUISHED_NAME VARCHAR2(512 BYTE),
EXTERNAL_PRINCIPAL VARCHAR2(255 BYTE) NOT NULL,
EMAIL_ADDRESS VARCHAR2(255 BYTE),
COMMON_NAME VARCHAR2(255 BYTE)
);
--Insert Statements
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LAST_LOGIN_DATE, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, ENCRYPTED_PASSWORD, EXTERNAL_PRINCIPAL)
Values
(2, 'test_user', 'test_user', 0, 0,
'N', 'N', TO_DATE('03/04/2015 17:21:10', 'MM/DD/YYYY HH24:MI:SS'), 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:09:26', 'MM/DD/YYYY HH24:MI:SS'), 'mu0ECfCtU+DPPvhI6W6QH0lzwtKvEO==',
'test_user');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, EXTERNAL_PRINCIPAL, COMMON_NAME)
Values
(6, 'UG1', 'UG1', 0, 1,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:10:57', 'MM/DD/YYYY HH24:MI:SS'), 'UG1', 'UG1');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, EXTERNAL_PRINCIPAL, COMMON_NAME)
Values
(7, 'UG2', 'UG2', 6, 1,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:11:07', 'MM/DD/YYYY HH24:MI:SS'), 'UG2', 'UG2');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, EXTERNAL_PRINCIPAL, COMMON_NAME)
Values
(8, 'UG3', 'UG3', 7, 1,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:11:18', 'MM/DD/YYYY HH24:MI:SS'), 'UG3', 'UG3');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, ENCRYPTED_PASSWORD, EXTERNAL_PRINCIPAL, EMAIL_ADDRESS, COMMON_NAME)
Values
(9, 'U1', 'U1', 8, 0,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:11:27', 'MM/DD/YYYY HH24:MI:SS'), '4tgwQcysfIs2dOSlws4/K3hEU==',
'U1', 'U1@test.com', 'U1');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, ENCRYPTED_PASSWORD, EXTERNAL_PRINCIPAL, EMAIL_ADDRESS, COMMON_NAME)
Values
(10, 'U2', 'U2', 8, 0,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:11:45', 'MM/DD/YYYY HH24:MI:SS'), 'vXefS6ygctDj86N/ySmGpfRJ3pmdKvP==',
'U2', 'U2@test.com', 'U2');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, ENCRYPTED_PASSWORD, EXTERNAL_PRINCIPAL, EMAIL_ADDRESS, COMMON_NAME)
Values
(11, 'U3', 'U3', 8, 0,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:12:03', 'MM/DD/YYYY HH24:MI:SS'), '8p2fZ/IOA3xNlOwN4vKuoORoQOPDA50==',
'U3', 'U3@test.com', 'U3');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, ENCRYPTED_PASSWORD, EXTERNAL_PRINCIPAL, EMAIL_ADDRESS, COMMON_NAME)
Values
(12, 'U5', 'U5', 18, 0,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:12:29', 'MM/DD/YYYY HH24:MI:SS'), 'NwO6Hf2JPcl+mAM3sPhY0o40csEj9x==',
'UG4', 'UG4@test.com', 'UG4');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, ENCRYPTED_PASSWORD, EXTERNAL_PRINCIPAL, EMAIL_ADDRESS, COMMON_NAME)
Values
(13, 'U4', 'U4', 8, 0,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:13:00', 'MM/DD/YYYY HH24:MI:SS'), 'vOXQA+A+Drqc3kWAUil+hPcPrbZ5sPhZ==',
'U4', 'U4@test.com', 'U4');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, EXTERNAL_PRINCIPAL, COMMON_NAME)
Values
(18, 'UG5', 'UG5', 7, 1,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:26:00', 'MM/DD/YYYY HH24:MI:SS'), 'UG5', 'UG5');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, ENCRYPTED_PASSWORD, EXTERNAL_PRINCIPAL, EMAIL_ADDRESS, COMMON_NAME)
Values
(19, 'U6', 'U6', 18, 0,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:26:33', 'MM/DD/YYYY HH24:MI:SS'), 'qtW3vObCT5CI4Abwsup70GBS8l9b==',
'U6', 'U6@test.com', 'U6');
Insert into USERS
(USERID, USERNAME, FULLNAME, USER_GROUP, USER_TYPE, IS_DELETED, IS_PROFILE_MANAGER, LOCKED_OUT_INDICATOR, LOCK_OUT_REASON, NUM_FAILED_ATTEMPTS, DISABLE_PASSWORD_EXPIRY, FORCE_PASSWORD_CHANGE, LAST_PASSWORD_CHANGE, ENCRYPTED_PASSWORD, EXTERNAL_PRINCIPAL, EMAIL_ADDRESS, COMMON_NAME)
Values
(20, 'U7', 'U7', 6, 0,
'N', 'N', 0, ' ',
0, 'N', 'N', TO_DATE('03/04/2015 17:27:00', 'MM/DD/YYYY HH24:MI:SS'), '63+W0YRVIOVT30E5xT82Fp5krxff==',
'U7', 'U7@test.com', 'U7');
COMMIT;
--Hierarchical Query to get the parent hierarchy for the userid=13
with user_hierarchy as (
select userid, username, user_group, user_type, level as level_num
from as_users
start with userid = 13
connect by prior user_group = userid)
select userid, username, user_group, user_type, level_num
from user_hierarchy;
--OutPut from the query
USERID USERNAME USER_GROUP USER_TYP LEVEL_NUM
13 U4 8 0 1
8 UG3 7 1 2
7 UG2 6 1 3
6 UG1 0 1 4
The above query will return the parent hierarchy for the given child (userid=13 in this case).My requirement is to write the above query without using the 'connect by prior' clause and none of the inbuilt support from the database.
The reason I need to write this simple code without using any inbuilt support from the database is because, I need to get the above hierarchical output on the Sybase ASE database which does not support recursive queries.I think the solution is to build a logic using some temporary tables, but I am not able to build the logic to get the desired output.
Can some one please help me with this. This is somewhat urgent. Thanks in advance for the help.
|
|
|
|
|
|
|
|
| Re: Writing a Hierarchical Query without using the connect by prior [message #634164 is a reply to message #634122] |
Thu, 05 March 2015 01:24  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Reposted messageReason: Michel, I agree that the issue is not linked to Oracle but still here I just need the logic if someone is aware of the same, and I could implement the it for Sybase. I have searched for all possible help from Sybase but unfortunately I did not get the required help. Please can you unblock the ticketso that some one could help me. Thanks Sachin
I will unlock the topic but I'm quite sure no one here will spend much time to do what Oracle natively does.
[Edit: Littlefoot has already unlocked it.  ]
[Updated on: Thu, 05 March 2015 01:25] Report message to a moderator |
|
|
|
| Re: Writing a Hierarchical Query without using the connect by prior [message #634182 is a reply to message #634122] |
Thu, 05 March 2015 05:40 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I believe SYBASE same as MS SQL and Oracle support recursive subquery factoring:
SQL> with user_hierarchy as (
2 select userid, username, user_group, user_type, level as level_num
3 from users
4 start with userid = 13
5 connect by prior user_group = userid)
6 select userid, username, user_group, user_type, level_num
7 from user_hierarchy
8 /
USERID USERNAME USER_GROUP USER_TYPE LEVEL_NUM
---------- ---------- ---------- ---------- ----------
13 U4 8 0 1
8 UG3 7 1 2
7 UG2 6 1 3
6 UG1 0 1 4
SQL> with r(
2 userid,
3 username,
4 user_group,
5 user_type,
6 level_num
7 ) as (
8 select userid,
9 username,
10 user_group,
11 user_type,
12 1 level_num
13 from users
14 where userid = 13
15 union all
16 select u.userid,
17 u.username,
18 u.user_group,
19 u.user_type,
20 r.level_num + 1 level_num
21 from r,
22 users u
23 where u.userid = r.user_group
24 )
25 select userid,
26 username,
27 user_group,
28 user_type,
29 level_num
30 from r
31 /
USERID USERNAME USER_GROUP USER_TYPE LEVEL_NUM
---------- ---------- ---------- ---------- ----------
13 U4 8 0 1
8 UG3 7 1 2
7 UG2 6 1 3
6 UG1 0 1 4
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 20:42:28 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
