Correlation - 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

Correlation

Author

Topic

Joseph_Amon
Starting Member

1 Post

Posted - 2009-11-14 : 04:26:35

HI All,
While trying to execute this view in sql, i got the error message as,"Msg 1013, Level 16, State 1, Procedure sale1, Line 3
The objects "dbo.INV1" and "dbo.INV1" in the FROM clause have the same exposed names. Use correlation names to distinguish them."
How do i correct it? I am unable to figure this out. Please suggest.

USE [DBNAME]
GO
/****** Object: View [dbo].[sale1] Script Date: 11/14/2009 14:05:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[sale1]
AS
SELECT DISTINCT
TOP (100) PERCENT dbo.OINV.DocDate AS Saldate, dbo.OINV.DocNum AS SalDoc, dbo.INV1.ItemCode AS SalItemcode, dbo.INV1.Dscription,
dbo.INV1.Quantity, dbo.INV1.Price, dbo.ODLN.DocNum AS delivery, dbo.OINV.CardCode,
(SELECT Block
FROM dbo.OCRD
WHERE (CardCode = dbo.OINV.CardCode)) AS block,
(SELECT City
FROM dbo.OCRD AS OCRD_2
WHERE (CardCode = dbo.OINV.CardCode)) AS city,
(SELECT State1
FROM dbo.OCRD AS OCRD_1
WHERE (CardCode = dbo.OINV.CardCode)) AS state, dbo.INV1.AcctCode, dbo.OINV.CardName, dbo.OINV.DocEntry, dbo.INV1.LineNum,
dbo.INV1.LineTotal, dbo.OINV.DocTotal
FROM dbo.OINV INNER JOIN
dbo.INV1 ON dbo.OINV.DocEntry = dbo.INV1.DocEntry LEFT OUTER JOIN
dbo.INV1 ON dbo.ODLN.DocEntry = dbo.INV1.BaseEntry LEFT OUTER JOIN
dbo.ODLN ON dbo.DLN1.DocEntry = dbo.ODLN.DocEntry

ORDER BY SalDoc

Thanks in advance,
Joseph

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-14 : 08:35:10

You are joining dbo.INV1 twice so you have to give different alias names for them.
For example you can do it like this:


dbo.INV1 as Inv1_Doc ON dbo.OINV.DocEntry = Inv1_Doc.DocEntry LEFT OUTER JOIN
dbo.INV1 as Inv1_Base ON dbo.ODLN.DocEntry = Inv1_Base.BaseEntry

Now you have use that aliases in your select list and WHERE-clause instead of dbo.INV1

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources