create 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 2005 Forums

Transact-SQL (2005)

create view

Author

Topic

mkool
Starting Member

25 Posts

Posted - 2008-02-15 : 15:58:12

these r important fields in 2 tables. i have to write a view that joins these 2 tables on snumber and pulls out columns below…

salehist
---------
SNUMBER
STYPE
ITEMNMBR
QUANTITY

SaleAmnt
---------
STYPE
SNUMBER
ORIGTYPE
ORIGNUMB
DCDATE
CITY
STATE
ZIP

i don't have any idea about view...
can anbody help me to create view using these 2table joins.

thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-15 : 16:20:34

First, write a SELECT statement that produces the output you want from your view.

Then add the following in front of the SELECT:

Create view dbo.MyViewName as

CODO ERGO SUM

mkool
Starting Member

25 Posts

Posted - 2008-02-15 : 16:30:45

create view salehistamtvw
as

select SNUMBER,STYPE,ITEMNMBR,QUANTITY from SALEHIST S2
inner join (select SNUMBER,STYPE,ORIGTYPE,ORIGNUMB,DOCDATE,CITY,STATE,ZIP from SALEAMNT ) S3
on S2.SNUMBER= S3.SNUMBER
where S2.STYPE = S3.STYPE
GO

IT GIVES ME ERROR:
Ambiguous column name 'STYPE' AND 'SNUMBER'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-15 : 16:41:08

You should alway fully qualify column names with the table alias in your SELECT statement.

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 01:23:19

quote:
Originally posted by mkool

create view salehistamtvw
as

select SNUMBER,STYPE,ITEMNMBR,QUANTITY from SALEHIST S2
inner join (select SNUMBER,STYPE,ORIGTYPE,ORIGNUMB,DOCDATE,CITY,STATE,ZIP from SALEAMNT ) S3
on S2.SNUMBER= S3.SNUMBER
where S2.STYPE = S3.STYPE
GO

IT GIVES ME ERROR:
Ambiguous column name 'STYPE' AND 'SNUMBER'

Think about error. Ambiguos column. See your query. You have STYPE & SNUMBER coming from both the tables (SALEHIST & SALEAMNT). You have just specified column names in select list but havent told query analyser from which table it should retrieve the field. Thats what its complaining about. So you need to specify from which table you want the fields from and that can be done by fully qualifying the columns as MVJ suggested. either use TableName.FieldName or TableAlias.FieldName. Also,If you have an alias defined always better to use it rather than table name

mkool
Starting Member

25 Posts

Posted - 2008-02-19 : 08:43:35

Thanks for your advise...and help..

i keep alias name

create view salehistamtvw
as

select s2.SNUMBER,s2.STYPE,s2.ITEMNMBR,s2.QUANTITY from SALEHIST S2
inner join (select s3.SNUMBER,s3.STYPE,s3.ORIGTYPE,s3.ORIGNUMB,s3.DOCDATE,s3.CITY,s3.STATE,s3.ZIP from SALEAMNT ) S3
on S2.SNUMBER= S3.SNUMBER
where S2.STYPE = S3.STYPE
GO

but it gives meerror like:
The multi-part identifier "s3.SNUMBER" could not be bound.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 08:49:44

quote:
Originally posted by mkool

Thanks for your advise...and help..

i keep alias name

create view salehistamtvw
as

select s2.SNUMBER,s2.STYPE,s2.ITEMNMBR,s2.QUANTITY from SALEHIST S2
inner join (select s3.SNUMBER,s3.STYPE,s3.ORIGTYPE,s3.ORIGNUMB,s3.DOCDATE,s3.CITY,s3.STATE,s3.ZIP from SALEAMNT ) S3
on S2.SNUMBER= S3.SNUMBER
where S2.STYPE = S3.STYPE
GO

but it gives meerror like:
The multi-part identifier "s3.SNUMBER" could not be bound.

Take the aliases from all fields of inner query. the alias is not available inside. You actually do not need alias there as you have only a single table involved. Usually alisses are used when you have more than one table and especially when it involves some columns with duplicate names from both tables. so you require only this:-

create view salehistamtvw
as

select s2.SNUMBER,s2.STYPE,s2.ITEMNMBR,s2.QUANTITY from SALEHIST S2
inner join (select SNUMBER,STYPE,ORIGTYPE,ORIGNUMB,DOCDATE,CITY,STATE,ZIP 
from SALEAMNT ) S3 
on S2.SNUMBER= S3.SNUMBER
and S2.STYPE = S3.STYPE 
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 08:49:54

That should be

create view salehistamtvw
as

select s2.SNUMBER,s2.STYPE,s2.ITEMNMBR,s2.QUANTITY from SALEHIST S2
inner join (select SNUMBER,STYPE,ORIGTYPE,ORIGNUMB,DOCDATE,CITY,STATE,ZIP from SALEAMNT ) S3
on S2.SNUMBER= S3.SNUMBER
where S2.STYPE = S3.STYPE
GO

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 08:50:46

Madhivanan

Failing to plan is Planning to fail

mkool
Starting Member

25 Posts

Posted - 2008-02-19 : 09:07:00

Thanks all..got it..

will remember always about aliases...

thanks for ur help and guidance..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources