I need to build a hierarchical query where I'm working on financial project. We have a list of Fiscal Monitors that will be assigned to departments which rolls up to the VPs. Transactions are read and they have the organization that the transaction belongs to. However, I do not want to have to assign Fiscal at the department level. I want to assign the Fiscal Montiors at the VP Level. So I need to query through the ORG table to find the VP level based on the departments ORG so I can assign the correct fiscal monitor to the transaction. I was getting help in another forum however I just found out it was an ORACLE forum and we are using SQL Server. Here is what they suggested to me:with ftvorgn as (select '330800' as org_code, 'F00147' as predecessor, 'CONVERT' AS NEXT_PREDECESSOR, 'GEOLOGICAL SCIENCES' from dual union select '330800', 'F00147', 'CONVERT', 'GEOLOGICAL SCIENCES' from dual union select '330800', 'F00147', 'SUAGUILA', 'GEOLOGICAL SCIENCES' from dual union select 'F00147', 'E00147', 'CONVERT', 'Geological Sciences 3' from dual union select 'E00147', 'C00132', 'CONVERT', 'Black Op incursions' from dual union select 'C00132', 'B00032', 'MERROSS', 'Arts and Sciences College' from dual)select substr(path,1,instr(path,'-')-1) base, substr(path,instr(path,'-',-1)+1,length(path)-instr(path,'-',-1)) top from ( select substr(max(sys_connect_by_path(org_code,'-')),2) path from ftvorgn connect by org_code = prior predecessor start with org_code = '330800' and next_predecessor <> 'CONVERT' )
I will get my hands on SQL server tomorrow when I get to the office. Is there a big difference in how SQL Server handles hierarchical queries as to ORACLE?
Hierarchical Queries
Posted - 2008-07-22 : 17:57:02
