Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  Conversion from PL/SQL to T-SQL

Author  Topic  sachinj Starting Member 1 Post Posted - 2012-02-06 : 02:08:28 I have below Oracle query that needs to be converted to T-SQL ( SQL Server 2005/2008 compatible). Please could any one help me with this. SELECT CONNECT_BY_ROOT user_id user_id, acct_id, acct_name, 'Yes' property FROM (SELECT a.*, u.user_id, u.acct_id u_acct_id FROM accounts a, user_account u WHERE a.acct_id = u.acct_id(+) AND u.property(+) = 'Yes') t START WITH acct_id = u_acct_id CONNECT BY acct_id = PRIOR acct_parent ORDER BY user_id, acct_id Below is the DDL/DMLs to create the schema and data : --Create Tables CREATE TABLE ACCOUNTS ( ACCT_ID INTEGER NOT NULL, ACCT_NAME VARCHAR(25) NOT NULL, ACCT_PARENT INTEGER, ACCT_TYPE INTEGER NOT NULL ); CREATE TABLE USER_ACCOUNT ( USER_ID INTEGER NOT NULL, ACCT_ID INTEGER NOT NULL, PROPERTY VARCHAR(15) NOT NULL ); --Insert values into tables INSERT INTO ACCOUNTS (ACCT_ID,ACCT_NAME,ACCT_PARENT,ACCT_TYPE) VALUES (1,'Group1',0,1); INSERT INTO ACCOUNTS (ACCT_ID,ACCT_NAME,ACCT_PARENT,ACCT_TYPE) VALUES (2,'Group2',1,1); INSERT INTO ACCOUNTS (ACCT_ID,ACCT_NAME,ACCT_PARENT,ACCT_TYPE) VALUES (3,'Group3',2,1); INSERT INTO ACCOUNTS (ACCT_ID,ACCT_NAME,ACCT_PARENT,ACCT_TYPE) VALUES (4,'Account1', 3,0); INSERT INTO ACCOUNTS (ACCT_ID,ACCT_NAME,ACCT_PARENT,ACCT_TYPE) VALUES (5,'Account2',3,0); INSERT INTO ACCOUNTS (ACCT_ID,ACCT_NAME,ACCT_PARENT,ACCT_TYPE) VALUES (6,'Account3',2,0); INSERT INTO ACCOUNTS (ACCT_ID,ACCT_NAME,ACCT_PARENT,ACCT_TYPE) VALUES (7,'Account4',0,0); INSERT INTO ACCOUNTS (ACCT_ID,ACCT_NAME,ACCT_PARENT,ACCT_TYPE) VALUES (8,'Account5',1,0); INSERT INTO USER_ACCOUNT (USER_ID, ACCT_ID, PROPERTY) VALUES (1, 4, 'Yes'); INSERT INTO USER_ACCOUNT (USER_ID, ACCT_ID, PROPERTY) VALUES (2, 5, 'No'); INSERT INTO USER_ACCOUNT (USER_ID, ACCT_ID, PROPERTY) VALUES (3, 6, 'Yes'); -------------------------------------------------- ACCOUNTS table stores results in parent child relationship format. ACCT_TYPE=0 in ACCOUNTS table denotes leaf node. ACCT_ID ACCT_NAME ACCT_PARENT ACCT_TYPE ---------- -------------------------------------------------- ----------- ---------- 1 Group1 0 1 2 Group2 1 1 3 Group3 2 1 4 Account1 3 0 5 Account2 3 0 6 Account3 2 0 7 Account4 0 0 8 Account5 1 0 Another table USER_ACCOUNT stores user permissions on the leaf nodes from ACCOUNTS table. USER_ID ACCT_ID PROPERTY ---------- ---------- ---------- 1 4 Yes 2 5 No 3 6 Yes This shows user=1 has permission on Account=4 , user=2 does not have access priviledge on Account=5. Now my requirement is to write a SQL query such that if the user has permissions on a given leaf node then all the parent / grand parent nodes should also be returned. Expected output for me would be : USER_ID ACCT_ID ACCT_NAME PROPERTY ---------- ---------- -------------------------------------------------- ----------- 3 1 Group1 Yes 3 2 Group2 Yes 3 6 Account3 Yes 1 1 Group1 Yes 1 2 Group2 Yes 1 3 Group3 Yes 1 4 Account1 Yes sachin jauhari