Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Unanswered: The multi-part identifier .... could not be bound.

    Hello,

    Fairly new SQL person here. I have searched around but think my problem is too specific.

    I'm connecting to SQL Server 2005 using a DQY (created by someone else) and tried to add one new select which is highlighted.


    SELECT 'Meds In Charts but NOT in History' as Note,
    tblResult.name as Patient,
    tblResultItems.name as Medication,
    tblResultItems.description as Prescription,
    cast(tblResult.date as date) as [Treatment date],
    [User].lastname as Nurse,
    (SELECT Clinic.Name FROM Clinic join Patient ON
    Clinic.AssociationIDNumber=Patient.DefaultClinic where Patient.patientid = tblResult.patientid) AS Facility

    FROM tblResultItems
    JOIN tblResult
    ON tblResultItems.tblResultid=tblResult.ID
    join [User]
    ON tblResultItems.edituser = [User].useridnumber
    where [type]='Medication'
    AND isNull(DoneNotDonePostpone,0)=1
    AND tblResultItems.RxID Not IN (Select MedPrescIDNumber
    FROM RunMedsActualAdministered
    WHERE RunIDNumber = tblResult****nIDNumber)
    AND tblResult.date Between ? AND ?


    But when running this I receive:
    The multi-part identifier "tblResult.date" could not be bound.


    Any help?
    Thanks!
    Cort

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    looks like this was designed to run on SQL 2008 from this line:
    Code:
    cast(tblResult.date as date) as [Treatment date],
    Check the tblResult table in your database. I am willing to bet that there is no column named "date".

  3. #3
    Join Date
    Oct 2009
    Posts
    3
    I just checked and the field is "date". The query was working prior to me adding a new line so the column name shouldn't be an issue I hope.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    cast(tblResult.[date] as date) as [Treatment date],
    I don't think this will help, but in any way it's never a good idea to use reserved words for table/column/.. names. "date", "User" and "type" are being (re-/mis-)used as names.

    One can use "[" and "]" to use those names nonetheless, but you know what they say when you use a certain environment: "Swim with the flow, not against it". Each time you have to type "[" and "]", you are swimming upstream.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I suspect that tblResult is not a very permanent table. The error messages are usually quite accurate. What happens when you try this:
    Code:
    SELECT 'Meds In Charts but NOT in History' as Note,
    	tblResult.name as Patient,
    	tblResultItems.name as Medication,
    	tblResultItems.description as Prescription,
    	cast(tblResult.date as date) as [Treatment date],
    	[User].lastname as Nurse,
    	Clinic.Name AS Facility
    FROM tblResultItems JOIN 
    	tblResult ON tblResultItems.tblResultid=tblResult.ID join 
    	[User] ON tblResultItems.edituser = [User].useridnumber join
    	patient on patient.patientid = tblResult.patientid join
    	clinic on Clinic.AssociationIDNumber=Patient.DefaultClinic 
    where [type]='Medication'
      AND isNull(DoneNotDonePostpone,0)=1
      AND tblResultItems.RxID Not IN (Select MedPrescIDNumber
    				FROM RunMedsActualAdministered
    				WHERE RunIDNumber = tblResult****nIDNumber)
      AND tblResult.date Between ? AND ?

  6. #6
    Join Date
    Oct 2009
    Posts
    3
    Thanks to all the responses! First off, I agree with not using reserved words for column names and I would never create a column called "date". So I tried putting brackets around date and found no difference in the query results.

    Second, I tried using the join statement that MCrowley provided and that works great! I had attempted adding some joins early on but was unsuccessful, so my thanks go out to both of you.

    Just so I know for the future, is including a select statment, like I tried, within the select recommended? Is there a good use for those type of calls?

    Thanks again,
    Cort

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I do not think it is recommended, unless there is no way around it. Any SELECT statement within the SELECT clause will be executed once per row.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •