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_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.
DelboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DelboyAuthor Commented:
PS, If I substitue the dbo.Company.CompanyPK for an actual string '12345ETC' it runs ok..
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
DelboyAuthor Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
DelboyAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
DelboyAuthor Commented:
Hi

It is a table valued function..

When I run the Cross Apply I get the same message as the Outer Apply   :)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
DelboyAuthor Commented:
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
DelboyAuthor Commented:
Error in bmp attached
Error.bmp
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the query designer? aaaaaaah.
please try to run the query directly in a query window ...
0
DelboyAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
DelboyAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DelboyAuthor Commented:
Suuuuperman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.