x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 5 replies
  • Answers 2 answers
  • Subscribers 78 subscribers
  • Views 1510 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
Michele Gaw

SQL error vKeywordSearchListOpportunity

SUGGESTED
Posted By

When reviewing a client's SQL logs, I am seeing the following error repeated over and over again.

Has anyone seen this error?

I would appreciate any assistance in resolving the error.  Thank you!

SELECT * FROM vKeywordSearchListOpportunity WHERE Oppo_OpportunityId=-1 ORDER BY Oppo_OpportunityId Invalid object name 'vKeywordSearchListOpportunity'

  • 0
    SUGGESTED

    Hi Michele

    What happens if you edit (but don't change) then save the vKeywordSearchListOpportunity? 

    Does it then still report an issue?

  • 0 in reply to Matthew Shaw

    I get the following error.

    Dec 23 2019 14:43:01.916	4928	6972	1	execsql,time,sql,errormsg	47	CREATE VIEW vKeywordSearchListOpportunity AS SELECT vPersonPE.*, Opportunity.*, vCompanyPE.*, OpportunityProgress.Oppo_ProgressNote FROM Opportunity LEFT JOIN vCompanyPE ON Opportunity.Oppo_PrimaryCompanyId = Comp_CompanyId LEFT JOIN vPersonPE ON Opportunity.Oppo_PrimaryPersonId = Pers_PersonId LEFT JOIN OpportunityProgress ON OpportunityProgress.Oppo_OpportunityId = Opportunity.Oppo_OpportunityId WHERE Opportunity.Oppo_Deleted IS NULL
	Column names in each view or function must be unique. Column name 'Oppo_ProgressNote' in view or function 'vKeywordSearchListOpportunity' is specified more than once
  • 0 in reply to Michele Gaw

    Hi Michele,

    OK so it looks like whatever joins you have in the view have called for a column of the same name to be called twice in the view (which whilst in SQL is OK, but in CRM it hates that). 

    Please post what the view vKeywordSearchOpportunity has in it and we'll see what the problem is. 

  • 0 in reply to Matthew Shaw

    I really appreciate your assistance on this!  This is what the view indicates when I open it...

    CREATE VIEW vKeywordSearchListOpportunity AS SELECT vPersonPE.*, Opportunity.*, vCompanyPE.*, OpportunityProgress.Oppo_ProgressNote FROM Opportunity LEFT JOIN vCompanyPE ON Opportunity.Oppo_PrimaryCompanyId = Comp_CompanyId LEFT JOIN vPersonPE ON Opportunity.Oppo_PrimaryPersonId = Pers_PersonId LEFT JOIN OpportunityProgress ON OpportunityProgress.Oppo_OpportunityId = Opportunity.Oppo_OpportunityId WHERE Opportunity.Oppo_Deleted IS NULL

  • 0 in reply to Michele Gaw
    SUGGESTED

    If you add 

    as oppo_progressnote1 

    after 

    OpportunityProgress.Oppo_ProgressNote

    i.e 

    OpportunityProgress.Oppo_ProgressNote AS oppo_progressnote1

    does it work?

    basically as you have opportunity.* it is returning all opportunity fields but the view has been changed to include the opportunity progress table which has the same field names which is not allowed by Sage CRM.

*Community Hub is the new name for Sage City