Solved

Multi-Part Identifier Missing

Posted on 2009-07-10
3
348 Views
Last Modified: 2012-05-07
Experts,

The attached stored proc is getting an error on t.MemberGUID:

"The multi-part identifier 't.MemberGUID" could not be found."

The last time I has this error, it was becasue I was referencing a field without referencing the table in the FROM clause.  This is clearly not the case here.  Ideas?
SELECT Member.MemberID, '01/01/1900' as servicedate

, 'HRA-Triglyceride' as loincCode,null as cpt_Code

, a.hramadAnswer as llc_Value 

FROM HRAMemAnswerData a WITH(NOLOCK), PopulationTask t, HRAGroupQuestions gq, HRAQuestion q  

INNER JOIN Member ON Member.MemberGUID=t.MemberGUID 

where a.PopulationTaskID = t.PopulationTaskID And a.HragqID = gq.HragqID 

And gq.HragqChildqID = q.HraqID and hraqid = 19 and Member.MemberID = @MemberID 

AND T.Active = 0 

AND LEFT(a.hramadAnswer, 3) = 'Yes' AND LTRIM(RTRIM(SUBSTRING(a.hramadAnswer, 5, LEN(a.hramadAnswer)))) != ''  and Member.ClientID = @ClientID  and Member.MemberID = @MemberID ORDER BY ServiceDate DESC

Open in new window

0
Comment
Question by:NigelRocks
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24822510
do not mix join and non join syntax...

SELECT Member.MemberID, '01/01/1900' as servicedate
, 'HRA-Triglyceride' as loincCode,null as cpt_Code
, a.hramadAnswer as llc_Value 
FROM HRAMemAnswerData a WITH(NOLOCK)
JOIN PopulationTask t
  ON a.PopulationTaskID = t.PopulationTaskID 
 AND T.Active = 0 
JOIN HRAGroupQuestions gq
  ON a.HragqID = gq.HragqID 
JOIN HRAQuestion q  
  ON gq.HragqChildqID = q.HraqID 
 AND q.hraqid = 19 
JOIN Member m 
  ON m.MemberGUID=t.MemberGUID 
 AND m.MemberID = @MemberID 
 AND m.ClientID = @ClientID  
WHERE LEFT(a.hramadAnswer, 3) = 'Yes' 
  AND LTRIM(RTRIM(SUBSTRING(a.hramadAnswer, 5, LEN(a.hramadAnswer)))) != ''  
ORDER BY ServiceDate DESC

Open in new window

0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24822566
Silly question, but: is there a field called MemberGUID in the table PopulationTask? - MemberID is used elsewhere in the query
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 24822577
I cleaned up the query a bit to see what it was doing. (Making inner joins out of classical joins, removing duplicate MemberID conditions...) You haven't specified which table the hraqid and ServiceDate fields come from., but other than that it's pretty clear where everything comes from.

Check that you actually have a MemberGUID field in the PopulationTask table, and that you have spelled it correctly.
select

   m.MemberID,

   '01/01/1900' as servicedate,

   'HRA-Triglyceride' as loincCode,

   null as cpt_Code,

   a.hramadAnswer as llc_Value 

from

   HRAMemAnswerData a WITH(NOLOCK),

   inner join PopulationTask t on t.PopulationTaskID = a.PopulationTaskID

   inner join HRAGroupQuestions gq on gq.HragqID = a.HragqID

   inner join HRAQuestion q on q.HraqID = gq.HragqChildqID

   inner join Member m ON m.MemberGUID = t.MemberGUID

where

   hraqid = 19 and

   t.Active = 0 and

   left(a.hramadAnswer, 3) = 'Yes' and

   ltrim(rtrim(substring(a.hramadAnswer, 5, len(a.hramadAnswer)))) != '' and

   m.ClientID = @ClientID and

   m.MemberID = @MemberID

order by

   ServiceDate desc

Open in new window

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 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

22 Experts available now in Live!

Get 1:1 Help Now