Solved

Multi-Part Identifier Missing

Posted on 2009-07-10
3
349 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Date Range Syntax Access 2003 10 54
PL/SQL LOOP CURSOR 3 58
Script to randomly create characters in MS SQL 6 32
how to install/upgrade the Blitz responder kit 8 24
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

17 Experts available now in Live!

Get 1:1 Help Now