Combining Views - 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 2005 Forums

Transact-SQL (2005)

Combining Views

Author

Topic

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-11-22 : 09:46:38

I have two views.
One links policies when the first letter is not C.
The second links a different part of the policy number when the first letter is C. How can i do both of these is one view.

Here is the sql to the individual views-

SELECT UH_PST05_1.UH_POLICY
FROM dbo.AssetAccount INNER JOIN
JohnTest.dbo.UH_PST05 AS UH_PST05_1 ON RIGHT(dbo.AssetAccount.AssetAccountRef, 8)
= UH_PST05_1.UH_POLICY COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE (NOT (LEFT(UH_PST05_1.UH_POLICY, 1) = 'C'))
GROUP BY dbo.AssetAccount.AssetAccountRef, LEFT(UH_PST05_1.UH_POLICY, 1), UH_PST05_1.UH_POLICY

SELECT UH_PST05_1.UH_POLICY
FROM dbo.AssetAccount INNER JOIN
JohnTest.dbo.UH_PST05 AS UH_PST05_1 ON LEFT(RIGHT(dbo.AssetAccount.AssetAccountRef, 8), 7) = LEFT(UH_PST05_1.UH_POLICY,
7) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE (LEFT(UH_PST05_1.UH_POLICY, 1) = 'C')
GROUP BY dbo.AssetAccount.AssetAccountRef, LEFT(UH_PST05_1.UH_POLICY, 1), UH_PST05_1.UH_POLICY

My aim is to combine these views into one. Any help would be much appreciated.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-22 : 09:52:09

Would a UNION ALL do the trick?

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-11-22 : 10:00:29

I was thinking of just writing as one view like

SELECT UH_PST05_1.UH_POLICY
FROM dbo.AssetAccount INNER JOIN

JohnTest.dbo.UH_PST05 AS UH_PST05_1 ON RIGHT(dbo.AssetAccount.AssetAccountRef, 8)
= UH_PST05_1.UH_POLICY COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE (NOT (LEFT(UH_PST05_1.UH_POLICY, 1) = 'C'))

JohnTest.dbo.UH_PST05 AS UH_PST05_1 ON LEFT(RIGHT(dbo.AssetAccount.AssetAccountRef, 8), 7) = LEFT(UH_PST05_1.UH_POLICY,
7) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE (LEFT(UH_PST05_1.UH_POLICY, 1) = 'C')

GROUP BY dbo.AssetAccount.AssetAccountRef, LEFT(UH_PST05_1.UH_POLICY, 1), UH_PST05_1.UH_POLICY

This obviously doesnt work, but i was wondering if you can do anything similar to this? Link tables when X= a and then link differently when X= b

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-22 : 10:04:19

[code]
SELECT UH_PST05_1.UH_POLICY
FROM dbo.AssetAccount INNER JOIN
JohnTest.dbo.UH_PST05 AS UH_PST05_1 ON RIGHT(dbo.AssetAccount.AssetAccountRef, 8)
= UH_PST05_1.UH_POLICY COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE (NOT (LEFT(UH_PST05_1.UH_POLICY, 1) = 'C'))
GROUP BY dbo.AssetAccount.AssetAccountRef, LEFT(UH_PST05_1.UH_POLICY, 1), UH_PST05_1.UH_POLICY

UNION ALL

SELECT UH_PST05_1.UH_POLICY
FROM dbo.AssetAccount INNER JOIN
JohnTest.dbo.UH_PST05 AS UH_PST05_1 ON LEFT(RIGHT(dbo.AssetAccount.AssetAccountRef, 8), 7) = LEFT(UH_PST05_1.UH_POLICY,
7) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE (LEFT(UH_PST05_1.UH_POLICY, 1) = 'C')
GROUP BY dbo.AssetAccount.AssetAccountRef, LEFT(UH_PST05_1.UH_POLICY, 1), UH_PST05_1.UH_POLICY

[/code]

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-11-22 : 11:12:58

That works, cheers for that.

How do i now create that into a table. I know i need to use INTO. but where do i put it?

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-22 : 11:16:24

Why do you need that in a table? Could you create a view instead?

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-11-22 : 11:34:54

I need to create them into a table so i can then use them in another view. The tables involved are massive and it all seems to run quicker when the policies are combined into a table.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-22 : 11:53:56

If it were me, I'd create a view from the code above, then use that view wherever else you need it.

Because chances are, if you need it in one view now, you'll need it other views later, and stored procedures, and probably some other places, too.

Oh, and I would add a column that has the ID field(s), just to make things easier on yourself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources