Create view with same field name.

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

Create view with same field name.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-12-19 : 22:37:52

Dear All,

I am having following query I want to create view on this.
But I am getting foolowing error. But I need these fields.
Can anybody please tell me how solve this issue ?

"Column names in each view or function must be unique. Column name 'PRDCT' in view or function 'VW_PV_OV_DETAILS' is specified more than once."

CREATE VIEW [dbo].[VW_PV_OV_DETAILS] AS
SELECT DISTINCT F43121.PRDCT,F43121.PRDOC,F43121.PRDGL,
F43121.PRKCOO,F43121.PRAN8,X.PRDCT,X.PRDOC
FROM F43121 AS F43121 (NOLOCK) INNER JOIN(SELECT DISTINCT F43121.PRKCOO,F43121.PRAN8,F43121.PRDCT,F43121.PRDOC,F43121.PRDOCO,F43121.PRNLIN FROM F43121 AS F43121 (NOLOCK) WHERE F43121.PRDCT = 'PV'
AND F43121.PRMATC = '2' AND F43121.PRDOC = '9002020' AND F43121.PRNLIN = '1')X ON X.PRKCOO = F43121.PRKCOO AND X.PRAN8 = F43121.PRAN8 AND X.PRDOCO = F43121.PRDOCO AND X.PRNLIN = F43121.PRNLIN
AND F43121.PRMATC = '1' AND F43121.PRDCT = 'OV' AND F43121.PRKCO = '00300'

Thanks and Regard's
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 00:13:09

thats because of duplicate columns with same name. Add an alias to one of them with some other name


CREATE VIEW [dbo].[VW_PV_OV_DETAILS] AS
SELECT DISTINCT F43121.PRDCT,F43121.PRDOC,F43121.PRDGL, 
F43121.PRKCOO,F43121.PRAN8,X.[Someother alias name here],X.PRDOC
FROM F43121 AS F43121 (NOLOCK) INNER JOIN(SELECT DISTINCT F43121.PRKCOO,F43121.PRAN8,F43121.PRDCT AS [Someother alias name here],F43121.PRDOC,F43121.PRDOCO,F43121.PRNLIN FROM F43121 AS F43121 (NOLOCK) WHERE F43121.PRDCT = 'PV' 
AND F43121.PRMATC = '2' AND F43121.PRDOC = '9002020' AND F43121.PRNLIN = '1')X ON X.PRKCOO = F43121.PRKCOO AND X.PRAN8 = F43121.PRAN8 AND X.PRDOCO = F43121.PRDOCO AND X.PRNLIN = F43121.PRNLIN
AND F43121.PRMATC = '1' AND F43121.PRDCT = 'OV' AND F43121.PRKCO = '00300'

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

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-12-20 : 00:35:47

Thnks. I resoled it.

Thanks and Regard's
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 00:40:04

wc

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

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-20 : 07:56:40

...and no one mentions that they should lose (nolock)

A predominate theme for noobs who think it improves performance

...sorry..I have been decoding Access/SQL Sever Views lately...

from high priced...."Consultants"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources