Incorrect syntax near the keyword "left"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Incorrect syntax near the keyword "left"

Author

Topic

kdjonesmtb
Starting Member

3 Posts

Posted - 2012-01-30 : 11:49:37

Hello,

Here is a query I running to identify specific concept values by their GUIDS

If I remove the U_member_concept_value eth code it works but when I add it back in I get the syntax error. I need to add ten more specific guid con concept identifiers

select top 10 c.CID, c.ssn,
d.externalId, c.FIRST_NAME, c.LAST_NAME,
f.effective_date coveff, f.term_date covterm,
g.effective_date eligeff, g.term_date eligterm, con.str_value con,
race.str_value race, eth.str_value eth

from MEMBER c (nolock)
inner join EXTERNAL_MEMBER_DATA d (nolock) on c.CID = d.CID
inner join MEMBER_COVERAGE f (nolock) on
c.CID = f.CID
and Getdate() between f.effective_date and f.term_date
inner join MEMBER_ELIGIBILITY g (nolock) on
f.id = g.coverage_id
and Getdate() between g.effective_date and g.term_date
left outer join ORG_PHYSICIAN_PROVIDER opp (nolock) on
c.CID = opp.CID
and opp.mask in (0, 2)
and Getdate() between opp.effective_date and opp.term_date
left outer join ORG_PHYSICIAN op (nolock) on
opp.PHYSICIAN_ID = op.id

inner join U_MEMBER_CONCEPT_VALUE con
on c.CID = con.CID
and con.concept_id = 1827 and con.str_value = 1

left outer join U_MEMBER_CONCEPT_VALUE race
on race.CID = d.CID
left outer join Concept
on race.concept_id = concept.id
where concept.guid = '66E9CD74'

left outer join U_MEMBER_CONCEPT_VALUE eth
on eth.CID = d.CID
left outer join Concept
on eth.concept_id = concept.id
where concept.guid = 'B17F2596'

Keith David Jones Senior Business Analyst

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-30 : 12:09:32

Ignoring the syntax error for a moment, why the nolocks everywhere? Are the users happy with inconsistent, duplicate and missing data?
See - [url]http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx[/url]

As for the syntax error, last 'paragraph' there's a join that's after the where clause. All joins are part of the from clause and must appear before the WHERE.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 12:33:31

you've two where clauses hence the error


..
left outer join U_MEMBER_CONCEPT_VALUE race
on race.CID = d.CID
left outer join Concept 
on race.concept_id = concept.id
where concept.guid = '66E9CD74'

should be modified as

left outer join U_MEMBER_CONCEPT_VALUE race
on race.CID = d.CID
left outer join Concept 
on race.concept_id = concept.id
and concept.guid = '66E9CD74'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kdjonesmtb
Starting Member

3 Posts

Posted - 2012-01-30 : 13:09:13

Hello,

I made the change you suggested and I am now receiving the following:

Msg 1013, Level 16, State 1, Line 1
The objects "Concept" and "Concept" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Keith David Jones Senior Business Analyst

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-30 : 13:21:21

The same table appears twice in the FROM clause with no aliases. Alias one or both.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 13:29:59

quote:
Originally posted by kdjonesmtb

Hello,

I made the change you suggested and I am now receiving the following:

Msg 1013, Level 16, State 1, Line 1
The objects "Concept" and "Concept" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Keith David Jones Senior Business Analyst

seems like you've cut and pasted parts of lots of small queries together.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources