Delboy
asked on
Multi Part Identifier Problem
Hi Guys.. I have used the SQL Server view designer to create a 'simple' view and when I run it I get Multi-Part Identifier dbo.Company.CompanyPK could not be bound.. Here is the query (With an alias version which also gave the same message)
SELECT dbo.Owner.OwnerPK, dbo.Company.CompanyPK, fun_Balances_OwnerGroups_1 .Balance
FROM dbo.Owner INNER JOIN
dbo.Company ON dbo.Owner.CompanyFK = dbo.Company.CompanyPK LEFT OUTER JOIN
dbo.fun_Balances_OwnerGrou ps(GETDATE (), dbo.Company.CompanyPK) AS fun_Balances_OwnerGroups_1 ON
dbo.Owner.OwnerPK = fun_Balances_OwnerGroups_1 .OwnerGrou pFK
SELECT Jim.OwnerPK, Tom.CompanyPK, fun_Balances_OwnerGroups_1 .Balance
FROM dbo.Owner AS Jim INNER JOIN
dbo.Company AS Tom ON Jim.CompanyFK = Tom.CompanyPK INNER JOIN
dbo.fun_Balances_OwnerGrou ps(GETDATE (), Tom.CompanyPK) AS fun_Balances_OwnerGroups_1 ON
Jim.OwnerPK = fun_Balances_OwnerGroups_1 .OwnerGrou pFK
the CompanyPK field is unique to the company table and does not exist in the other table or function..
Your help is appreciated..
Derek.
SELECT dbo.Owner.OwnerPK, dbo.Company.CompanyPK, fun_Balances_OwnerGroups_1
FROM dbo.Owner INNER JOIN
dbo.Company ON dbo.Owner.CompanyFK = dbo.Company.CompanyPK LEFT OUTER JOIN
dbo.fun_Balances_OwnerGrou
dbo.Owner.OwnerPK = fun_Balances_OwnerGroups_1
SELECT Jim.OwnerPK, Tom.CompanyPK, fun_Balances_OwnerGroups_1
FROM dbo.Owner AS Jim INNER JOIN
dbo.Company AS Tom ON Jim.CompanyFK = Tom.CompanyPK INNER JOIN
dbo.fun_Balances_OwnerGrou
Jim.OwnerPK = fun_Balances_OwnerGroups_1
the CompanyPK field is unique to the company table and does not exist in the other table or function..
Your help is appreciated..
Derek.
try this:
SELECT o.OwnerPK, c.CompanyPK, f.Balance
FROM dbo.Owner o
JOIN dbo.Company c
ON o.CompanyFK = c.CompanyPK
OUTER APPLY dbo.fun_Balances_OwnerGroups(GETDATE(), c.CompanyPK) AS f
ON o.OwnerPK = f.OwnerGroupFK
ASKER
Hi angellll
I get this when I run it.. The OUTER APPLY SQL construct or statement is not supported. I am using SQL Server 2005 NOT Express Edition
I get this when I run it.. The OUTER APPLY SQL construct or statement is not supported. I am using SQL Server 2005 NOT Express Edition
>I am using SQL Server 2005 NOT Express Edition
you mean, standard edition?
anyhow, the OUTER APPLY is available in all editions of 2005 except possibly the CE edition.
apart from that, please check if you are using sql 2005 client connected to a sql 2000 instance, or if the database is in sql 2000 compatibility mode (value = 80) , and increase it to 90 (sql 2005)
you mean, standard edition?
anyhow, the OUTER APPLY is available in all editions of 2005 except possibly the CE edition.
apart from that, please check if you are using sql 2005 client connected to a sql 2000 instance, or if the database is in sql 2000 compatibility mode (value = 80) , and increase it to 90 (sql 2005)
ASKER
Hi angellll
The database is in Compatibility mode for 2005 (90) already and we use the developer edition which I presume has the required level.. We are all 2005 client and server here too..
D.
The database is in Compatibility mode for 2005 (90) already and we use the developer edition which I presume has the required level.. We are all 2005 client and server here too..
D.
is the function a table-valued function or a single-valued function?
what about using CROSS APPLY:
what about using CROSS APPLY:
SELECT o.OwnerPK, c.CompanyPK, f.Balance
FROM dbo.Owner o
JOIN dbo.Company c
ON o.CompanyFK = c.CompanyPK
CROSS APPLY dbo.fun_Balances_OwnerGroups(GETDATE(), c.CompanyPK) AS f
ON o.OwnerPK = f.OwnerGroupFK
ASKER
Hi
It is a table valued function..
When I run the Cross Apply I get the same message as the Outer Apply :)
It is a table valued function..
When I run the Cross Apply I get the same message as the Outer Apply :)
I feared so.
well, I don't know, then. can you show the FULL error message, and eventually the actual sql that you run.
also, the output of:
SELECT @@VERSION
well, I don't know, then. can you show the FULL error message, and eventually the actual sql that you run.
also, the output of:
SELECT @@VERSION
ASKER
Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86)
Mar 3 2007 18:40:02
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Mar 3 2007 18:40:02
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
ASKER
Error in bmp attached
Error.bmp
Error.bmp
the query designer? aaaaaaah.
please try to run the query directly in a query window ...
please try to run the query directly in a query window ...
ASKER
Ok.. Did that and get this messaeg in the query window ..
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'ON'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'ON'.
I see:
SELECT o.OwnerPK, c.CompanyPK, f.Balance
FROM dbo.Owner o
JOIN dbo.Company c
ON o.CompanyFK = c.CompanyPK
CROSS APPLY dbo.fun_Balances_OwnerGroups(GETDATE(), c.CompanyPK) AS f
WHERE o.OwnerPK = f.OwnerGroupFK
ASKER
That worked.. can you amend it to show all rows from Owner so I get record whether there is a balance or not? and I will leave you alone..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Suuuuperman
ASKER