Solved

Multi Part Identifier Problem

Posted on 2009-07-02
16
285 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
Comment Utility
PS, If I substitue the dbo.Company.CompanyPK for an actual string '12345ETC' it runs ok..
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
Hi

It is a table valued function..

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

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Delboy
Comment Utility
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
Comment Utility
Error in bmp attached
Error.bmp
0
 
LVL 142

Expert Comment

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

Author Comment

by:Delboy
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
Comment Utility
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
Comment Utility
Suuuuperman
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now