Page 1 of 1
Table name not matched
Posted: Wed Oct 11, 2006 9:22 am
by CarolP
Hello,____One of our clients gets the following error when running a report. I can^t reproduce it running against their data restored to our server and no other client has reported it. CONTRACTS2 is a view and exists with SELECT granted to PUBLIC. ____I hope someone can cast some light on this____Carol______Error Message=DB access: [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix ^CONTRACTS2^ does not match with a table name or alias name used in the query.[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.__
=> RE: Table name not matched
Posted: Wed Oct 11, 2006 12:09 pm
by kfleming
What is the SELECT statement that is used in the report?____Kathleen__R&R Support
==> RE: Table name not matched
Posted: Tue Oct 17, 2006 8:48 am
by Carol_P_(Guest)
select__ CONTRACTS2."CONNO", CONTRACTS2."CONREF", CONTRACTS2."DEALDATE", CONTRACTS2."CONDATE", CONTRACTS2."ACTPUBDATE", CONTRACTS2."PUBDATE", CONTRACTS2."QTY", CONTRACTS2."PRICE", CONTRACTS2."REVDATE", CONTRACTS2."ADVCURR", CONTRACTS2."NOTES", CONTRACTS2."COMMENTS", CONTRACTS2."ACTREVDATE", CONTRACTS2."ROYALTY", CONTRACTS2."CANCELDATE", CONTRACTS2."COMMR", CONTRACTS2."PUBNAME", CONTRACTS2."SUBNAME", CONTRACTS2."RIGDESC", CONTRACTS2."TERRDESC", CONTRACTS2."LANGDESC", CONTRACTS2."FMTDESC", CONTRACTS2."TITNO", CONTRACTS2."UBAL", CONTRACTS2."DF", CONTRACTS2."PAYCURR", Titles2."TITNO", Titles2."TITLE", Titles2."ED", Titles2."PUBDATE", Titles2."ISBN", Titles2."SERDESC", Titles2."IMPDESC", Titles2."PROPLASTNAME", Titles2."PROPFIRSTNAME", Titles2."MAINAUT", Titles2."AUTNO", Titles2."AUTLASTNAME", Titles2."AUTFIRSTNAME", Titles2."DIVDESC", Titles2."EDITOR", Titles2."DEPT", Titles2."DEPTNAME", Titles2."SHORTNAME", CURR."CURRDESC", CTIT."TITNO", CTIT."QTY", CTIT."PRICE", EXCH."CURRNO1", EXCH."EXRATE", RSYS."CURRNO", RSYS."IND2", RSYS."COMPANYNAME", cCURR."CURRDESC", CONVALUE."CONVALUE", CONTIT1."TITNO", ROYSUM1."ROYSUM", TIT."TITLE", TIT."ED", SCURR."CURRDESC"__ from __CONTRACTS2, Titles2, CURR CURR, CTIT, EXCH, RSYS, CURR cCURR, CONVALUE, CONTIT1, ROYSUM1, TIT, CURR SCURR __where__ CONTRACTS2."TITNO" = Titles2."TITNO" __and CONTRACTS2."ADVCURR" = CURR."CURRNO" __and CONTRACTS2."CONNO" = CTIT."CONNO" __and CONTRACTS2."ADVCURR" = EXCH."CURRNO" __and 1 = RSYS."SYSREC" __and CONTRACTS2."CONCURR" = cCURR."CURRNO" __and CONTRACTS2."CONNO" = CONVALUE."CONNO" __and CONTRACTS2."CONNO" = CONTIT1."CONNO" __and CONTRACTS2."CONNO" = ROYSUM1."CONNO" __and CTIT."TITNO" = TIT."TITNO" __and RSYS."CURRNO" = SCURR."CURRNO" __and ((EXCH."CURRNO1" = RSYS."CURRNO")) __
==> RE: Table name not matched
Posted: Tue Oct 17, 2006 8:50 am
by Carol_P_(Guest)
select__ CONTRACTS2."CONNO", CONTRACTS2."CONREF", CONTRACTS2."DEALDATE", CONTRACTS2."CONDATE", CONTRACTS2."ACTPUBDATE", CONTRACTS2."PUBDATE", CONTRACTS2."QTY", CONTRACTS2."PRICE", CONTRACTS2."REVDATE", CONTRACTS2."ADVCURR", CONTRACTS2."NOTES", CONTRACTS2."COMMENTS", CONTRACTS2."ACTREVDATE", CONTRACTS2."ROYALTY", CONTRACTS2."CANCELDATE", CONTRACTS2."COMMR", CONTRACTS2."PUBNAME", CONTRACTS2."SUBNAME", CONTRACTS2."RIGDESC", CONTRACTS2."TERRDESC", CONTRACTS2."LANGDESC", CONTRACTS2."FMTDESC", CONTRACTS2."TITNO", CONTRACTS2."UBAL", CONTRACTS2."DF", CONTRACTS2."PAYCURR", Titles2."TITNO", Titles2."TITLE", Titles2."ED", Titles2."PUBDATE", Titles2."ISBN", Titles2."SERDESC", Titles2."IMPDESC", Titles2."PROPLASTNAME", Titles2."PROPFIRSTNAME", Titles2."MAINAUT", Titles2."AUTNO", Titles2."AUTLASTNAME", Titles2."AUTFIRSTNAME", Titles2."DIVDESC", Titles2."EDITOR", Titles2."DEPT", Titles2."DEPTNAME", Titles2."SHORTNAME", CURR."CURRDESC", CTIT."TITNO", CTIT."QTY", CTIT."PRICE", EXCH."CURRNO1", EXCH."EXRATE", RSYS."CURRNO", RSYS."IND2", RSYS."COMPANYNAME", cCURR."CURRDESC", CONVALUE."CONVALUE", CONTIT1."TITNO", ROYSUM1."ROYSUM", TIT."TITLE", TIT."ED", SCURR."CURRDESC"__ from __CONTRACTS2, Titles2, CURR CURR, CTIT, EXCH, RSYS, CURR cCURR, CONVALUE, CONTIT1, ROYSUM1, TIT, CURR SCURR __where__ CONTRACTS2."TITNO" = Titles2."TITNO" __and CONTRACTS2."ADVCURR" = CURR."CURRNO" __and CONTRACTS2."CONNO" = CTIT."CONNO" __and CONTRACTS2."ADVCURR" = EXCH."CURRNO" __and 1 = RSYS."SYSREC" __and CONTRACTS2."CONCURR" = cCURR."CURRNO" __and CONTRACTS2."CONNO" = CONVALUE."CONNO" __and CONTRACTS2."CONNO" = CONTIT1."CONNO" __and CONTRACTS2."CONNO" = ROYSUM1."CONNO" __and CTIT."TITNO" = TIT."TITNO" __and RSYS."CURRNO" = SCURR."CURRNO" __and ((EXCH."CURRNO1" = RSYS."CURRNO")) __
===> RE: Table name not matched
Posted: Tue Oct 17, 2006 2:14 pm
by kfleming
Nothing appears incorrect in the SQL. Probably the only way to troubleshoot this would be to try different variants of the Select to see what the client environment does not like.____I would start with using just the CONTRACTS2 table and fields and then start adding the joined tables and fields.____Kathleen__R&R Support____