Populating the Ragged ORG Bridge table

View previous topic View next topic Go down

Populating the Ragged ORG Bridge table

Post  epekarik on Fri Aug 17, 2012 3:27 pm

I have a working script from Ralph that populates the ragged Org Bridge table. It works great, but it was written with Oracle SQL. Unfortunetly it uses the Oracle "connect by prior" clause. Is there anyone out there who could translate this to MS SQL?


/* the Organization table */

Create table DIM_ORG (
ORG_KEY INTEGER NOT NULL,
ORG_NAME VARCHAR2(50),
.
PARENT_ORG_KEY INTEGER);


/* populate Bridge table */

CREATE or Replace procedure ORG_EXPLOSION_SP as

/* get org-s from org_dimension and put it into Cursor */

CURSOR Get_Roots is
select ORG_KEY as ROOT_KEY,
decode(PARENT_ORG_KEY, NULL,'Y','N') as HIGHEST_FLAG,
ORG_NAME as ROOT_ORG
from ORG;

/* load Bridge table */

BEGIN

For Roots in Get_Roots
LOOP
insert into Bridge_ORG
(PARENT_ORG_KEY,
CHILD_ORG_KEY,
LEVEL,
#_of_levels_from_Parent,
Bottom_Flag,
Top_Flag,
Load_DateTime)
/* */
select
roots.ROOT_KEY,
ORG_KEY,
LEVEL - 1,
ROWNUM,
'N',
roots.HIGHEST_FLAG,
(Select sysdate from dual)
from DIM_ORG
Start with ORG_KEY = roots.ROOT_KEY
connect by prior ORG_KEY = PARENT_KEY;
END LOOP;

update Bridge_ORG
SET Bottom_Flag = 'Y'
where not exists (select * from DIM_ORG
where PARENT_ORG_KEY = BRIDGE_ORG.CHILD_ORG_KEY);
COMMIT;

END;


Thank you
Endre
avatar
epekarik

Posts : 8
Join date : 2012-07-27
Age : 53
Location : Cincinnati, OH

View user profile

Back to top Go down

Re: Populating the Ragged ORG Bridge table

Post  epekarik on Thu Sep 06, 2012 10:53 pm


Just for reference---- Look at the "Populating the Bridge table (Tsql)" discussion. Very nice SQL solution to populate Bridge table, independent from Oracle , MS SQL or DB2
avatar
epekarik

Posts : 8
Join date : 2012-07-27
Age : 53
Location : Cincinnati, OH

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum