Creating view help - 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

General SQL Server Forums

New to SQL Server Programming

Creating view help

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-03 : 12:48:00

I am trying to create a view from two tables using union between the two tables.

The two tables are identical in their structure except one column and I need that column as part of the view

Background on these two tables
The frist table was created way back and had limitations to save data and the new table was created to store date with no limitations

Sample code

Create View [dbo].[VCust] as
select *
from
(
select Reqid,platform,
policy
from platform

UNION

Select RequestID,Platform,policy,sales from Policies

I know the above wont work as the second select has an additional column which is not in the first select statement

need help

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-03 : 13:07:05

select Reqid,platform,
policy
,[sales] = convert(int,null) -- assuming sales is an int data type
from platform

UNION

Select RequestID,Platform,policy,sales from Policies

Jim

Everyday I learn something that somebody else already knew

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-03 : 13:34:27

Thanks Jim
Works great.The datatype was varchar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-04 : 12:45:23

you can use UNION ALL if you dont have duplicates existing in data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-05 : 11:16:10

I tried using Union all and get the below error

Msg 205, Level 16, State 1, Procedure Customer, Line 10
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-05 : 11:22:08

That means that the top of your query has to have the same number of columns as the bottom part (and in the same order, same data types).

Jim

Everyday I learn something that somebody else already knew

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-05 : 11:45:54

yep.But we had a recommendation to use Union all.Not sure how it would work in this scenario

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-05 : 11:57:55

Can't help without seeing code.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-08 : 01:48:25

quote:
Originally posted by jim_jim

yep.But we had a recommendation to use Union all.Not sure how it would work in this scenario

show your sample data and then explain what you're trying to acieve. then we will clear about your scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources