Solved

Multi Part Identifier Problem

Posted on 2009-07-02
16
290 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

829 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