December 13, 2006 at 10:22 pm
Greeting everyone, here's a nutcracker for you guys to ponder...
I've been given a query whose purpose is to display data from tables withing two different databases. We are dealing with a rather complex third-party application, and to illustrate an example, in order to pull up very basic info from a user (name, address, state, country) we need to go through 5 different tables. The two databases are denoted by RE7 and CertifiedObservers, the query I've been given to troubleshoot returns and error message of:
"Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '.'."
the query goes as follows (sorry for it being rather large):
SELECT dbo.RE7.RECORDS.CONSTITUENT_ID, dbo.RE7.CONSTITUENT.KEY_NAME, dbo.RE7.CONSTITUENT.FIRST_NAME,
dbo.RE7.CONSTITUENT.MIDDLE_NAME,
dbo.RE7.member.MemID, dbo.RE7.membershipTransaction.ExpiresOn, dbo.RE7.ADDRESS.ADDRESS_BLOCK, dbo.RE7.ADDRESS.CITY,
dbo.RE7.ADDRESS.STATE,
dbo.RE7.ADDRESS.POST_CODE, dbo.RE7.TABLEENTRIES.LONGDESCRIPTION, dbo.RE7.PHONES.NUM, dbo.RE7.PHONES_1.NUM, dbo.CertifiedObservers.Obsvr_exp.tournament,
dbo.CertifiedObservers.Obsvr_exp.observe_date, dbo.CertifiedObservers.Obsvr_exp.blue, dbo.CertifiedObservers.Obsvr_exp.white, dbo.CertifiedObservers.Obsvr_exp.striped, dbo.CertifiedObservers.Obsvr_exp.black,
dbo.CertifiedObservers.Obsvr_exp.sail, dbo.CertifiedObservers.Obsvr_exp.spear,
dbo.CertifiedObservers.Obsvr_exp.sword, dbo.CertifiedObservers.Obsvr_exp.score, dbo.CertifiedObservers.Obsvr_exp.captain, dbo.CertifiedObservers.Obsvr_exp.phone, dbo.CertifiedObservers.Obsvr_exp.boatname, dbo.CertifiedObservers.Obsvr_exp.comments, dbo.CertifiedObservers.Obsvr_exp.tdcomments
FROM dbo.RE7.RECORDS RIGHT JOIN dbo.RE7.ADDRESS RIGHT JOIN dbo.RE7.CONSTIT_ADDRESS ON dbo.RE7.ADDRESS.ID = dbo.RE7.CONSTIT_ADDRESS.ADDRESS_ID
LEFT JOIN dbo.RE7.TABLEENTRIES ON dbo.RE7.ADDRESS.COUNTRY = dbo.RE7.TABLEENTRIES.TABLEENTRIESID
INNER JOIN dbo.RE7.member INNER JOIN dbo.RE7.CONSTITUENT ON dbo.RE7.member.ConstitID = dbo.RE7.CONSTITUENT.RECORDS_ID
ON dbo.RE7.CONSTIT_ADDRESS.CONSTIT_ID = dbo.RE7.CONSTITUENT.RECORDS_ID INNER JOIN dbo.RE7.membershipTransaction
ON dbo.member.ID = dbo.membershipTransaction.MembershipID ON dbo.RE7.RECORDS.ID = dbo.RE7.CONSTITUENT.RECORDS_ID
LEFT JOIN dbo.RE7.PHONES RIGHT JOIN dbo.RE7.CONSTIT_ADDRESS_PHONES
ON dbo.RE7.PHONES.PHONESID = dbo.RE7.CONSTIT_ADDRESS_PHONES.PHONESID ON dbo.RE7.CONSTIT_ADDRESS.ADDRESS_ID = dbo.RE7.CONSTIT_ADDRESS_PHONES.CONSTITADDRESSID
LEFT JOIN dbo.RE7.CONSTIT_ADDRESS_PHONES AS dbo.RE7.CONSTIT_ADDRESS_PHONES1 ON dbo.RE7.CONSTIT_ADDRESS.ADDRESS_ID = dbo.RE7.CONSTIT_ADDRESS_PHONES1.CONSTITADDRESSID
LEFT JOIN dbo.RE7.PHONES AS dbo.RE7.PHONES_1 ON dbo.RE7.CONSTIT_ADDRESS_PHONES_1.PHONESID = dbo.RE7.PHONES_1.PHONESID LEFT JOIN dbo.CertifiedObservers.Obsvr_exp ON dbo.RE7.RECORDS.CONSTITUENT_ID = dbo.CertifiedObservers.Obsvr_exp.RE_ConstituentID_Link
WHERE dbo.RE7.membershipTransaction.ExpiresOn GetDate AND dbo.RE7.membershipTransaction.Category=41 Or dbo.RE7.membershipTransaction.Category=42 Or dbo.RE7.membershipTransaction.Category=43 Or dbo.RE7.membershipTransaction.Category=44 AND dbo.RE7.CONSTIT_ADDRESS.TYPE=181 Or dbo.RE7.CONSTIT_ADDRESS.TYPE=1121
AND dbo.RE7.PHONES.PHONETYPEID=415 AND dbo.RE7.PHONES_1.PHONETYPEID=417 Or dbo.RE7.PHONES_1.PHONETYPEID=692
ORDER BY dbo.RE7.CONSTITUENT.KEY_NAME, dbo.RE7.CONSTITUENT.FIRST_NAME;
line 16 I've highlighted in red.
Thank you so much for your help!
December 13, 2006 at 10:56 pm
hi
is RE7 name of the database. if that is the case then should'nt it be RE7.dbo.[tablename].[fieldname]...
"Keep Trying"
December 13, 2006 at 10:59 pm
When you use the table alias use single name instead of three name...
LEFT JOIN dbo.RE7.CONSTIT_ADDRESS_PHONES AS CONSTIT_ADDRESS_PHONES1 ON dbo.RE7.CONSTIT_ADDRESS.ADDRESS_ID = CONSTIT_ADDRESS_PHONES1.CONSTITADDRESSID
MohammedU
Microsoft SQL Server MVP
December 14, 2006 at 2:17 am
The Order of User and Database is wrong in your statement.
ServerName.DatabaseName.OwnerName.ObjectName
It should be CertifiedObservers.dbo rather than dbo.CertifiedObservers. After this change the query will be like this and hopefully work fine for you.
SELECT RE7.dbo.RECORDS.CONSTITUENT_ID, RE7.dbo.CONSTITUENT.KEY_NAME, RE7.dbo.CONSTITUENT.FIRST_NAME,
RE7.dbo.CONSTITUENT.MIDDLE_NAME,
RE7.dbo.member.MemID, RE7.dbo.membershipTransaction.ExpiresOn, RE7.dbo.ADDRESS.ADDRESS_BLOCK, RE7.dbo.ADDRESS.CITY,
RE7.dbo.ADDRESS.STATE,
RE7.dbo.ADDRESS.POST_CODE, RE7.dbo.TABLEENTRIES.LONGDESCRIPTION, RE7.dbo.PHONES.NUM, RE7.dbo.PHONES_1.NUM, CertifiedObservers.dbo.Obsvr_exp.tournament,
CertifiedObservers.dbo.Obsvr_exp.observe_date, CertifiedObservers.dbo.Obsvr_exp.blue, CertifiedObservers.dbo.Obsvr_exp.white, CertifiedObservers.dbo.Obsvr_exp.striped, CertifiedObservers.dbo.Obsvr_exp.black,
CertifiedObservers.dbo.Obsvr_exp.sail, CertifiedObservers.dbo.Obsvr_exp.spear,
CertifiedObservers.dbo.Obsvr_exp.sword, CertifiedObservers.dbo.Obsvr_exp.score, CertifiedObservers.dbo.Obsvr_exp.captain, CertifiedObservers.dbo.Obsvr_exp.phone, CertifiedObservers.dbo.Obsvr_exp.boatname, CertifiedObservers.dbo.Obsvr_exp.comments, CertifiedObservers.dbo.Obsvr_exp.tdcomments
FROM RE7.dbo.RECORDS RIGHT JOIN RE7.dbo.ADDRESS RIGHT JOIN RE7.dbo.CONSTIT_ADDRESS ON RE7.dbo.ADDRESS.ID = RE7.dbo.CONSTIT_ADDRESS.ADDRESS_ID
LEFT JOIN RE7.dbo.TABLEENTRIES ON RE7.dbo.ADDRESS.COUNTRY = RE7.dbo.TABLEENTRIES.TABLEENTRIESID
INNER JOIN RE7.dbo.member INNER JOIN RE7.dbo.CONSTITUENT ON RE7.dbo.member.ConstitID = RE7.dbo.CONSTITUENT.RECORDS_ID
ON RE7.dbo.CONSTIT_ADDRESS.CONSTIT_ID = RE7.dbo.CONSTITUENT.RECORDS_ID INNER JOIN RE7.dbo.membershipTransaction
ON dbo.member.ID = dbo.membershipTransaction.MembershipID ON RE7.dbo.RECORDS.ID = RE7.dbo.CONSTITUENT.RECORDS_ID
LEFT JOIN RE7.dbo.PHONES RIGHT JOIN RE7.dbo.CONSTIT_ADDRESS_PHONES
ON RE7.dbo.PHONES.PHONESID = RE7.dbo.CONSTIT_ADDRESS_PHONES.PHONESID ON RE7.dbo.CONSTIT_ADDRESS.ADDRESS_ID = RE7.dbo.CONSTIT_ADDRESS_PHONES.CONSTITADDRESSID
LEFT JOIN RE7.dbo.CONSTIT_ADDRESS_PHONES AS RE7.dbo.CONSTIT_ADDRESS_PHONES1 ON RE7.dbo.CONSTIT_ADDRESS.ADDRESS_ID = RE7.dbo.CONSTIT_ADDRESS_PHONES1.CONSTITADDRESSID
LEFT JOIN RE7.dbo.PHONES AS RE7.dbo.PHONES_1 ON RE7.dbo.CONSTIT_ADDRESS_PHONES_1.PHONESID = RE7.dbo.PHONES_1.PHONESID LEFT JOIN CertifiedObservers.dbo.Obsvr_exp ON RE7.dbo.RECORDS.CONSTITUENT_ID = CertifiedObservers.dbo.Obsvr_exp.RE_ConstituentID_Link
WHERE RE7.dbo.membershipTransaction.ExpiresOn GetDate AND RE7.dbo.membershipTransaction.Category=41 Or RE7.dbo.membershipTransaction.Category=42 Or RE7.dbo.membershipTransaction.Category=43 Or RE7.dbo.membershipTransaction.Category=44 AND RE7.dbo.CONSTIT_ADDRESS.TYPE=181 Or RE7.dbo.CONSTIT_ADDRESS.TYPE=1121
AND RE7.dbo.PHONES.PHONETYPEID=415 AND RE7.dbo.PHONES_1.PHONETYPEID=417 Or RE7.dbo.PHONES_1.PHONETYPEID=692
ORDER BY RE7.dbo.CONSTITUENT.KEY_NAME, RE7.dbo.CONSTITUENT.FIRST_NAME;
cheers
December 14, 2006 at 2:27 am
Mohammed Uddin is right, you should also need to change the ALIAS in a single name as he mentioned.
There is another error in your query WHERE CLAUSE.
You made where clause as RE7.dbo.membershipTransaction.ExpiresOn GetDate but it would not work for you.
The right way is RE7.dbo.membershipTransaction.ExpiresOn = GetDate().
The Getdate function return the time as well with date so make sure you need the record of that date and time. if not then you need to change this to the date using convert function.
cheers
December 14, 2006 at 6:47 am
Step 1... For "queries from hell", the first step would be to "knock the hell out of it". Format the code so you can actually read it instead of having to study it ESPECIALLY if it's someone else's garbage. A key piece would be to assign and use MEANINGFUL table name aliases everywhere possible to simplify the code. Do logical line breaks on key words like SELECT, INNER JOIN, LEFT JOIN and ON.
That way, you can concentrate on problems like easily finding bad joins, etc. A side benefit of formatting the code for readability is that you can't help but read each line individually as you are doing it... many of the dozen of so errors you have in the code will simply bubble to the top as you format it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply