Link to home
Create AccountLog in
Avatar of larksys
larksys

asked on

SQL 2005 and CF8 - using SQL view "The multi-part identifier "UI.Current_Company_Number" could not be bound.

I am using a view created in SQL 2005;

SELECT     TOP (100) PERCENT UI.ID, UI.FirstName, UI.LastName, UI.Off_Limits_Flag, UI.Current_Company_Number, UI.Tax_Specialty_Id, UI.Current_Title,
                      USS.subspecialtyID, USS.percentage, USS.UserID, SS.SubSpecialtyID AS Expr1, SS.Title AS sstitle, SS.SpecialtyID
FROM         dbo.Users_Info AS UI LEFT OUTER JOIN
                          (SELECT     MAX(SubSpecialtyID) AS subspecialtyID, UserID, MAX(Percentage) AS percentage
                            FROM          dbo.Users_SubSpecialty
                            GROUP BY UserID) AS USS ON UI.ID = USS.UserID LEFT OUTER JOIN
                      dbo.SubSpecialty AS SS ON USS.subspecialtyID = SS.SubSpecialtyID AND UI.Specialty = SS.SpecialtyID
ORDER BY UI.LastName, USS.percentage DESC

and I get the following error from my CF page;

The multi-part identifier "UI.Current_Company_Number" could not be bound.

Here is the query;

<cffunction name="QGetSpecialtiesIndividuals" access="remote" returntype="query">
    <cfargument name="companynum" required="no">
    <cfargument name="specialty" required="no">
    <cfset var qgs="">
    <cfquery datasource="#request.dsn#" name="Qgs">
SELECT *
FROM Users_Subspecialties_Flowchart
WHERE UI.Current_Company_Number = #val(arguments.companynum)#
        AND UI.Tax_Specialty_ID = #arguments.specialty#

     
      </cfquery>
    <cfreturn (qgs) />
</cffunction>

I can combine the view SQL and my WHERE clause and it works. I just thought it would be more efficient to run it from a view.
ASKER CERTIFIED SOLUTION
Avatar of thecodist
thecodist
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You can also test executing the code in a query window by plugging in some specific values to see if there really is a problem with the query (replace 1234 and 5678 with actual values):

SELECT *
FROM Users_Subspecialties_Flowchart
WHERE Current_Company_Number = 1234
        AND Tax_Specialty_ID = 5678
Avatar of larksys
larksys

ASKER

Had to be something simple. Thanks