April 3, 2012 at 7:07 am
Hi experts,
here is the table ddl and sample data
CREATE TABLE TABLE1
(
OBJECT VARCHAR(30),
DEPENDENCY VARCHAR(30)
)
INSERT INTO TABLE1
SELECT 'VIEW1','TABLE1' UNION ALL
SELECT 'VIEW2','TABLE2' UNION ALL
SELECT 'VIEW3','VIEW2' UNION ALL
SELECT 'VIEW4','VIEW3' UNION ALL
SELECT 'VIEW5','TABLE1' UNION ALL
SELECT 'VIEW3','VIEW1' UNION ALL
SELECT 'VIEW5','VIEW4'
how to frame a query to select dependency order.
here TABLE1 and TABLE2 have no dependencies .
consider VIEW5 .. VIEW5 it depends on VIEW4 , VIEW4 depends on VIEW3 again VIEW3 depends on VIEW2 and VIEW2 depends on TABLE2
that means before creating VIEW5 .. TABLE2,VIEW2 ,VIEW3 ,VIEW4 should be created.
please advice me.
expected output:
TABLE1
TABLE2
VIEW1
VIEW2
VIEW3
VIEW4
VIEW5
April 3, 2012 at 7:24 am
SELECT * FROM TABLE1
ORDER BY DEPENDENCY
It appears from your sample data this would come out in the correct order, but if your sample data doesn't reflect your actual data, you should assign an ordinal to your data and add the column to TABLE1.
April 3, 2012 at 7:30 am
It is not about ORDER BY clause....
As per sample data to execute VIEW1 before we should create TABLE1 and TABLE2 So in this case the order of execution is TABLE1, TABLE2, VIEW1.. like so on ...
April 3, 2012 at 7:51 am
I don't understand what you're trying to do. Your DDL is incorrect, but I assumed you are trying to create a table. I don't see a view except some strings that say 'VIEW1...' etc.
Please post your correct DDL and the expected output.
Edit: looks like you corrected your DDL for the table, but I still don't see any views defined.
April 3, 2012 at 8:15 am
i mean
TABLE1 and TABLE2 have no dependencies .
consider VIEW5 .. VIEW5 it depends on VIEW4 , VIEW4 depends on VIEW3 again VIEW3 depends on VIEW2 and VIEW2 depends on TABLE2
that means before creating VIEW5 .. TABLE2,VIEW2 ,VIEW3 ,VIEW4 should be created.
April 3, 2012 at 8:23 am
--Fixed you sample data
SELECT [OBJECT],DEPENDENCY
INTO TABLE1
FROM (VALUES
('VIEW1','TABLE1'),
('VIEW2','TABLE2'),
('VIEW3','VIEW2'),
('VIEW4','VIEW3'),
('VIEW5','TABLE1'),
('VIEW3','VIEW1'),
('VIEW5','VIEW4'))a([OBJECT],DEPENDENCY);
--Best guess at what you want
WITH CTE AS (SELECT a.DEPENDENCY, a.[OBJECT], ISNULL([ORDER],0) AS [ORDER]
FROM TABLE1 a
OUTER APPLY (SELECT DEPENDENCY, 1
FROM TABLE1
WHERE a.DEPENDENCY = [OBJECT]) b(DEPENDENCY,[ORDER])),
CTE2 AS (SELECT DEPENDENCY, [OBJECT], [ORDER]
FROM CTE
WHERE [ORDER] = 0
UNION ALL
SELECT a.DEPENDENCY, a.[OBJECT], b.[ORDER]+1
FROM CTE a
INNER JOIN CTE2 b ON a.DEPENDENCY = b.OBJECT)
SELECT DEPENDENCY
FROM CTE2
GROUP BY DEPENDENCY, [ORDER]
ORDER BY [ORDER];
Produces: -
DEPENDENCY
----------
TABLE1
TABLE2
VIEW1
VIEW2
VIEW3
VIEW4
April 3, 2012 at 8:23 am
Steve Cullen (4/3/2012)
I don't understand what you're trying to do. Your DDL is incorrect, but I assumed you are trying to create a table. I don't see a view except some strings that say 'VIEW1...' etc.Please post your correct DDL and the expected output.
Edit: looks like you corrected your DDL for the table, but I still don't see any views defined.
The views and tables he is talking about is in the data. Looks like Hierarchical data. Unfortunately he has his dependencies slightly wrong based on his own data. VIEW3 is dependent on both VIEW1 and VIEW2.
April 3, 2012 at 8:42 am
Thanks
Cadavre and Lynn Pettis for your valuable replays
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply