?
Solved

Multi Part Identifier Problem

Posted on 2009-07-02
16
Medium Priority
?
303 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

765 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