Solved

Multi Part Identifier Problem

Posted on 2009-07-02
16
297 Views
Last Modified: 2012-05-07
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_OwnerGroups(GETDATE(), dbo.Company.CompanyPK) AS fun_Balances_OwnerGroups_1 ON
                      dbo.Owner.OwnerPK = fun_Balances_OwnerGroups_1.OwnerGroupFK

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_OwnerGroups(GETDATE(), Tom.CompanyPK) AS fun_Balances_OwnerGroups_1 ON
                      Jim.OwnerPK = fun_Balances_OwnerGroups_1.OwnerGroupFK

the CompanyPK field is unique to the company table and does not exist in the other table or function..  

Your help is appreciated..

Derek.
0
Comment
Question by:Delboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 

Author Comment

by:Delboy
ID: 24762809
PS, If I substitue the dbo.Company.CompanyPK for an actual string '12345ETC' it runs ok..
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24762828
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

Open in new window

0
 

Author Comment

by:Delboy
ID: 24763023
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
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24763150
>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)
0
 

Author Comment

by:Delboy
ID: 24763242
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24763430
is the function a table-valued function or a single-valued function?

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

Open in new window

0
 

Author Comment

by:Delboy
ID: 24763552
Hi

It is a table valued function..

When I run the Cross Apply I get the same message as the Outer Apply   :)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24763583
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
0
 

Author Comment

by:Delboy
ID: 24763632
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)


0
 

Author Comment

by:Delboy
ID: 24763657
Error in bmp attached
Error.bmp
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24763705
the query designer? aaaaaaah.
please try to run the query directly in a query window ...
0
 

Author Comment

by:Delboy
ID: 24763776
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'.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24763879
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

Open in new window

0
 

Author Comment

by:Delboy
ID: 24763910
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..
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 24764090
tricky, indeed :)
SELECT o.OwnerPK, c.CompanyPK, f.Balance
  FROM dbo.Owner o
  JOIN dbo.Company c
    ON o.CompanyFK = c.CompanyPK 
  LEFT JOIN (
     SELECT c.CompanyPK, f.OwnerGroupFK, f.Balance
       FROM dbo.Company c
       CROSS APPLY dbo.fun_Balances_OwnerGroups(GETDATE(), c.CompanyPK) AS f 
     ) f
    ON o.OwnerPK = f.OwnerGroupFK
   AND o.CompanyFK = f.CompanyPK 

Open in new window

0
 

Author Closing Comment

by:Delboy
ID: 31599166
Suuuuperman
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question