October 19, 2012 at 8:02 am
does anyone know what I have setup wrong here?
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT DISTINCT TAG_NAME = 'CUST_ALLFOOD', TAG_DATA =(M.GENERIC_NAME + CONVERT(VARCHAR(MAX),ca.Notes)
INNER JOIN CLIENT_ALLERGY CA (NOLOCK) ON C.OID_LINK = CA.OID
INNER JOIN MEDICATION M (NOLOCK) ON CA.MEDICATION_MONIKER = M.OID
INNER JOIN CROSSREF X (NOLOCK) ON M.CHEMICAL_REFERENCE_TYPE = X.OID_TO_REFERENCE
WHERE C.OID = @CA_OID
AND X.CROSSREF_TYPE_MONIKER = @MSDP_XREF_OID
AND X.Code = 'FOOD'
AND CA.Expdate IS NULL);
Why I get Incorrect syntax near the keyword 'INNER'.
on the first join?
Thanks
Joe
October 19, 2012 at 8:40 am
jbalbo (10/19/2012)
does anyone know what I have setup wrong here?INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT DISTINCT TAG_NAME = 'CUST_ALLFOOD', TAG_DATA =(M.GENERIC_NAME + CONVERT(VARCHAR(MAX),ca.Notes)
INNER JOIN CLIENT_ALLERGY CA (NOLOCK) ON C.OID_LINK = CA.OID
INNER JOIN MEDICATION M (NOLOCK) ON CA.MEDICATION_MONIKER = M.OID
INNER JOIN CROSSREF X (NOLOCK) ON M.CHEMICAL_REFERENCE_TYPE = X.OID_TO_REFERENCE
WHERE C.OID = @CA_OID
AND X.CROSSREF_TYPE_MONIKER = @MSDP_XREF_OID
AND X.Code = 'FOOD'
AND CA.Expdate IS NULL);
Why I get Incorrect syntax near the keyword 'INNER'.
on the first join?
Thanks
Joe
Because your closing ) is way at the end of your query instead of the end of the convert.
Why all the nolocks?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2012 at 9:44 am
Thanks... fixed it. dumb on my part... thanks
I don't know whats going on with the nolicks, I inherited this.
to be honest, newer at this and not sure what it does, I'll have to google...
Thanks Again
Joe
October 19, 2012 at 9:59 am
jbalbo (10/19/2012)
Thanks... fixed it. dumb on my part... thanksI don't know whats going on with the nolicks, I inherited this.
to be honest, newer at this and not sure what it does, I'll have to google...
Thanks Again
Joe
Sometime it just takes another set of eyes to see it. 😉
The NOLOCK hint can be very dangerous if accurate results are required from the query. They are often used as a way to "make it faster". It unfortunately is not a go fast button. It can actually read some data twice and skip other data. It is unpredictable when this will happen.
Here are a couple of articles that discuss the pitfalls of that query hint.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply