I need help with a View. - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

I need help with a View.

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-05-19 : 09:49:47

Hi Reader!

I'm having a bit of trouble producing a view. Below is an example table.


-------------------------------------------------------------
| CompanyType  | Phase | Measure | ChildType    | ChildPhase |
|--------------|-------|---------|--------------|------------|
| Construction | Roofs | Safety  | NULL         | NULL       |
| Construction | Roofs | Prices  | NULL         | NULL       |
| Construction | Roofs | Time    | NULL         | NULL       |
| Construction | Walls | Safety  | NULL         | NULL       |
| Construction | Walls | Prices  | NULL         | NULL       |
| Construction | Walls | Time    |              |            |
| Construction | All   | Walls   | Construction | Roofs      |
| Construction | All   | Roofs   | Construction | Walls      |
| Construction | All   | Other   | NULL         | NULL       |
--------------------------------------------------------------

A template is made up of Company Type and Phase, so I have the following templates: Construction Roofs, Construction Walls and Construction All

A template is made up of measures for example Construction Walls has three measure which are Safety, Prices and Time. A measure may use another template (a child template) to get a value. All I want to do is produce a list of Templates that do not have measures that use child templates. For example the view should produce the below results when using the above example table.

Construction Walls
Construction Roofs

Thats it. Also the Child Type and Child Phase can contain NULLs as well as nothing "".

Thank you very much for your time, I hope you can help.

Regards

Leah

Edited by - leahsmart on 05/19/2003 09:50:26

Edited by - leahsmart on 05/19/2003 09:51:10

dsdeming

479 Posts

Posted - 2003-05-19 : 13:49:17

Try this:

SET NOCOUNT ON

CREATE TABLE TmpTest
(
CompanyType varchar( 30 ) NOT NULL,
Phase varchar( 30 ) NOT NULL,
Measure varchar( 30 ),
ChildType varchar( 30 ),
ChildPhase varchar( 30 )
)

INSERT INTO TmpTest SELECT 'Construction', 'Roofs', 'Safety ', NULL, NULL
INSERT INTO TmpTest SELECT 'Construction', 'Roofs', 'Prices ', NULL, NULL
INSERT INTO TmpTest SELECT 'Construction', 'Roofs', 'Time ', NULL, NULL
INSERT INTO TmpTest SELECT 'Construction', 'Walls', 'Safety ', NULL, NULL
INSERT INTO TmpTest SELECT 'Construction', 'Walls', 'Prices ', NULL, NULL
INSERT INTO TmpTest SELECT 'Construction', 'Walls', 'Time ', '', ''
INSERT INTO TmpTest SELECT 'Construction', 'All ', 'Walls ', 'Construction', 'Roofs'
INSERT INTO TmpTest SELECT 'Construction', 'All ', 'Roofs ', 'Construction', 'Walls'
INSERT INTO TmpTest SELECT 'Construction', 'All ', 'Other ', NULL, NULL
GO

CREATE VIEW Tester
AS
SELECT DISTINCT CompanyType, Phase
FROM TmpTest
WHERE CompanyType + Phase NOT IN(
SELECT TestColumn = CompanyType + Phase FROM TmpTest WHERE COALESCE( ChildType + ChildPhase, '' ) <> ''
)
GO

SELECT * FROM Tester

DROP VIEW Tester
DROP TABLE TmpTest

Note that neither CompanyType nor Phase can be nullable.

HTH

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-05-20 : 04:04:16

Thank you very much, it works beautifully!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources