November 30, 2022 at 3:07 pm
I am getting the error:
Column names in each view or function must be unique. Column name 'Id' in view or function 'myview' is specified more than once.
CREATE VIEW myview
AS
SELECT
*
FROM dbo.appointment
LEFT OUTER JOIN dbo.ts_engagement
ON appointment.regardingobjectid = ts_engagement.ts_engagementid
WHERE ts_engagement.ts_engagementtype = 'CORE'
I appreciate column 'Id' appears in both tables, but I didn't think that this would prevent a view from being created.
Schema for appointment is as follows:
CREATE TABLE encrichmentdb.dbo.appointment (
Id NVARCHAR(50) NULL
,SinkCreatedOn DATETIME2 NULL
,SinkModifiedOn DATETIME2 NULL
,statecode NVARCHAR(50) NULL
,statuscode NVARCHAR(50) NULL
,ts_tone NVARCHAR(50) NULL
,ts_stageofmeetingrounds NVARCHAR(255) NULL
,ts_eventtype NVARCHAR(50) NULL
,ts_originationmeetingtype NVARCHAR(50) NULL
,onlinemeetingtype NVARCHAR(50) NULL
,ts_whooriginatedthemeeting NVARCHAR(50) NULL
,attachmenterrors NVARCHAR(50) NULL
,ts_talentmeetingtype NVARCHAR(255) NULL
,ts_meetingteam NVARCHAR(50) NULL
,ts_talentmeetingmethod NVARCHAR(255) NULL
,prioritycode NVARCHAR(50) NULL
,ts_meetingmethod NVARCHAR(50) NULL
,instancetypecode NVARCHAR(50) NULL
,ts_othermeetingmethod NVARCHAR(50) NULL
,ts_productsdiscussed NVARCHAR(100) NULL
,ts_contentcovered NVARCHAR(100) NULL
,isalldayevent BIT NULL
,ismapiprivate BIT NULL
,isonlinemeeting BIT NULL
,isworkflowcreated BIT NULL
,ts_keyiractivity BIT NULL
,ts_noninvestor BIT NULL
,isregularactivity BIT NULL
,isbilled BIT NULL
,ts_interview BIT NULL
,isdraft BIT NULL
,owninguser NVARCHAR(50) NULL
,owninguser_entitytype NVARCHAR(50) NULL
,owningteam NVARCHAR(255) NULL
,owningteam_entitytype NVARCHAR(255) NULL
,ts_officemet NVARCHAR(255) NULL
,ts_officemet_entitytype NVARCHAR(255) NULL
,serviceid NVARCHAR(255) NULL
,serviceid_entitytype NVARCHAR(255) NULL
,slainvokedid NVARCHAR(255) NULL
,slainvokedid_entitytype NVARCHAR(255) NULL
,slaid NVARCHAR(255) NULL
,slaid_entitytype NVARCHAR(255) NULL
,modifiedby NVARCHAR(50) NULL
,modifiedby_entitytype NVARCHAR(50) NULL
,owningbusinessunit NVARCHAR(50) NULL
,owningbusinessunit_entitytype NVARCHAR(50) NULL
,createdonbehalfby NVARCHAR(255) NULL
,createdonbehalfby_entitytype NVARCHAR(255) NULL
,modifiedonbehalfby NVARCHAR(50) NULL
,modifiedonbehalfby_entitytype NVARCHAR(50) NULL
,ts_taskowner NVARCHAR(255) NULL
,ts_taskowner_entitytype NVARCHAR(255) NULL
,transactioncurrencyid NVARCHAR(50) NULL
,transactioncurrencyid_entitytype NVARCHAR(50) NULL
,createdby NVARCHAR(50) NULL
,createdby_entitytype NVARCHAR(50) NULL
,regardingobjectid NVARCHAR(50) NULL
,regardingobjectid_entitytype NVARCHAR(50) NULL
,ts_investorgroup NVARCHAR(50) NULL
,ts_investorgroup_entitytype NVARCHAR(50) NULL
,ownerid NVARCHAR(50) NULL
,ownerid_entitytype NVARCHAR(50) NULL
,optionalattendees NVARCHAR(50) NULL
,requiredattendees NVARCHAR(50) NULL
,organizer NVARCHAR(50) NULL
,slaname NVARCHAR(255) NULL
,importsequencenumber NVARCHAR(255) NULL
,ts_meetingcity NVARCHAR(255) NULL
,onlinemeetingchatid NVARCHAR(100) NULL
,scheduleddurationminutes INT NULL
,ts_attendeestoaddtocrm NVARCHAR(50) NULL
,ts_investorgroupyominame NVARCHAR(255) NULL
,location NVARCHAR(150) NULL
,ts_investorgroupname NVARCHAR(100) NULL
,mpe_onsiteddtone NVARCHAR(255) NULL
,transactioncurrencyidname NVARCHAR(50) NULL
,modifiedbyname NVARCHAR(50) NULL
,scheduledstart DATETIME2 NULL
,ts_lpupdates NVARCHAR(255) NULL
,owneridname NVARCHAR(50) NULL
,ts_eventdescription NVARCHAR(255) NULL
,onlinemeetingid NVARCHAR(200) NULL
,ts_taskowneryominame NVARCHAR(255) NULL
,ts_executivesummary NVARCHAR(800) NULL
,createdonbehalfbyname NVARCHAR(255) NULL
,subject NVARCHAR(200) NULL
,mpe_onsiteddmainfocusareas NVARCHAR(255) NULL
,activityadditionalparams NVARCHAR(100) NULL
,ts_topicscovered NVARCHAR(2550) NULL
,slainvokedidname NVARCHAR(255) NULL
,createdbyname NVARCHAR(50) NULL
,formattedscheduledend NVARCHAR(255) NULL
,outlookownerapptid INT NULL
,exchangerate FLOAT NULL
,lastonholdtime NVARCHAR(255) NULL
,category NVARCHAR(255) NULL
,ts_tonepersonalities NVARCHAR(1150) NULL
,versionnumber INT NULL
,subcategory NVARCHAR(255) NULL
,regardingobjectidyominame NVARCHAR(255) NULL
,ts_purpose NVARCHAR(100) NULL
,ts_sectorscoveredbyfirm NVARCHAR(255) NULL
,attachmentcount INT NULL
,modifiedonbehalfbyname NVARCHAR(50) NULL
,serviceidname NVARCHAR(255) NULL
,scheduledend DATETIME2 NULL
,modifiedfieldsmask NVARCHAR(50) NULL
,createdon DATETIME2 NULL
,seriesid NVARCHAR(50) NULL
,overriddencreatedon NVARCHAR(255) NULL
,ts_opportunitiesdiscussed NVARCHAR(255) NULL
,regardingobjectidname NVARCHAR(100) NULL
,isunsafe INT NULL
,utcconversiontimezonecode INT NULL
,createdonbehalfbyyominame NVARCHAR(255) NULL
,subscriptionid NVARCHAR(255) NULL
,ts_objective NVARCHAR(100) NULL
,sortdate DATETIME2 NULL
,ts_taskduedate NVARCHAR(255) NULL
,actualstart NVARCHAR(255) NULL
,ts_taskdescription NVARCHAR(255) NULL
,processid NVARCHAR(255) NULL
,globalobjectid VARBINARY(MAX) NULL
,onlinemeetingjoinurl NVARCHAR(300) NULL
,actualdurationminutes INT NULL
,ts_theirprogramme NVARCHAR(3900) NULL
,ts_sectorscoveredids NVARCHAR(255) NULL
,createdbyyominame NVARCHAR(50) NULL
,owningbusinessunitname NVARCHAR(50) NULL
,mpe_onsiteddmeetingsummary NVARCHAR(255) NULL
,description NVARCHAR(MAX) NULL
,owneridyominame NVARCHAR(50) NULL
,modifiedon DATETIME2 NULL
,modifiedbyyominame NVARCHAR(50) NULL
,onholdtime NVARCHAR(255) NULL
,modifiedonbehalfbyyominame NVARCHAR(50) NULL
,ts_sourceid NVARCHAR(255) NULL
,activitytypecode NVARCHAR(50) NULL
,activityid NVARCHAR(50) NULL
,stageid NVARCHAR(255) NULL
,ts_conferencename NVARCHAR(50) NULL
,actualend DATETIME2 NULL
,ts_ifdeclinedreason NVARCHAR(255) NULL
,ts_taskownername NVARCHAR(255) NULL
,xpd_listofexternalattendees NVARCHAR(255) NULL
,regardingobjecttypecode NVARCHAR(255) NULL
,ts_originationcommentsnotes NVARCHAR(255) NULL
,formattedscheduledstart NVARCHAR(255) NULL
,originalstartdate DATETIME2 NULL
,owneridtype NVARCHAR(255) NULL
,traversedpath NVARCHAR(255) NULL
,safedescription NVARCHAR(255) NULL
,ts_officemetname NVARCHAR(255) NULL
,timezoneruleversionnumber INT NULL
,ts_notes NVARCHAR(MAX) NULL
)
GO
Schema for ts_engagement is as follows:
CREATE TABLE encrichmentdb.dbo.ts_engagement (
Id NVARCHAR(50) NULL
,SinkCreatedOn NVARCHAR(50) NULL
,SinkModifiedOn NVARCHAR(50) NULL
,statecode INT NULL
,statuscode NVARCHAR(50) NULL
,ts_roletype NVARCHAR(50) NULL
,ts_interimpermanent NVARCHAR(50) NULL
,ts_recruitmentstage NVARCHAR(50) NULL
,xpd_employentity NVARCHAR(50) NULL
,xpd_appointeesuccessrating NVARCHAR(50) NULL
,ts_engagementtype NVARCHAR(50) NULL
,xpd_montagulevel NVARCHAR(50) NULL
,mpe_flowlastrunoutcome NVARCHAR(50) NULL
,xpd_externalhireorinternalpromotion NVARCHAR(50) NULL
,xpd_vacancystatus NVARCHAR(50) NULL
,xpd_talentteaminvolvement NVARCHAR(50) NULL
,xpd_projectstatus NVARCHAR(50) NULL
,xpd_function NVARCHAR(50) NULL
,ts_levelofrecommendation NVARCHAR(50) NULL
,xpd_montaguoffice NVARCHAR(50) NULL
,xpd_level NVARCHAR(50) NULL
,ts_usesreviewed BIT NULL
,xpd_sharepointlocationcreated BIT NULL
,new_documentlocationcreated BIT NULL
,ts_approved BIT NULL
,xpd_formallyapproved BIT NULL
,new_createdocumentlocation BIT NULL
,xpd_remcoapproval BIT NULL
,xpd_inplanbudget BIT NULL
,ts_abort BIT NULL
,owninguser NVARCHAR(50) NULL
,owninguser_entitytype NVARCHAR(50) NULL
,createdonbehalfby NVARCHAR(255) NULL
,createdonbehalfby_entitytype NVARCHAR(255) NULL
,transactioncurrencyid NVARCHAR(50) NULL
,transactioncurrencyid_entitytype NVARCHAR(50) NULL
,mpe_assigntoteamid NVARCHAR(255) NULL
,mpe_assigntoteamid_entitytype NVARCHAR(255) NULL
,modifiedby NVARCHAR(50) NULL
,modifiedby_entitytype NVARCHAR(50) NULL
,owningbusinessunit NVARCHAR(50) NULL
,owningbusinessunit_entitytype NVARCHAR(50) NULL
,mpe_postengagementcontactsensitivityid NVARCHAR(255) NULL
,mpe_postengagementcontactsensitivityid_entitytype NVARCHAR(255) NULL
,ts_portfoliocompany NVARCHAR(50) NULL
,ts_portfoliocompany_entitytype NVARCHAR(50) NULL
,ts_portfoliocompanylead NVARCHAR(50) NULL
,ts_portfoliocompanylead_entitytype NVARCHAR(50) NULL
,ts_approver NVARCHAR(50) NULL
,ts_approver_entitytype NVARCHAR(50) NULL
,owningteam NVARCHAR(255) NULL
,owningteam_entitytype NVARCHAR(255) NULL
,xpd_montaguteammember NVARCHAR(50) NULL
,xpd_montaguteammember_entitytype NVARCHAR(50) NULL
,ts_engagementfirm NVARCHAR(50) NULL
,ts_engagementfirm_entitytype NVARCHAR(50) NULL
,mpe_montagulead NVARCHAR(50) NULL
,mpe_montagulead_entitytype NVARCHAR(50) NULL
,mpe_livecontactsensitivityid NVARCHAR(255) NULL
,mpe_livecontactsensitivityid_entitytype NVARCHAR(255) NULL
,ts_leadcontactatfirm NVARCHAR(50) NULL
,ts_leadcontactatfirm_entitytype NVARCHAR(50) NULL
,modifiedonbehalfby NVARCHAR(255) NULL
,modifiedonbehalfby_entitytype NVARCHAR(255) NULL
,xpd_fpplead NVARCHAR(255) NULL
,xpd_fpplead_entitytype NVARCHAR(255) NULL
,createdby NVARCHAR(50) NULL
,createdby_entitytype NVARCHAR(50) NULL
,ownerid NVARCHAR(50) NULL
,ownerid_entitytype NVARCHAR(50) NULL
,ts_targetsalary INT NULL
,ts_estimatedcost FLOAT NULL
,ts_finalcostofengagement FLOAT NULL
,ts_salaryofplacedcandidate_base FLOAT NULL
,ts_finalcostofengagement_base FLOAT NULL
,ts_estimatedcost_base FLOAT NULL
,ts_salaryofplacedcandidate INT NULL
,xpd_hrsalaryofplacedcandidate_base FLOAT NULL
,xpd_hrsalaryofplacedcandidate INT NULL
,ts_targetsalary_base FLOAT NULL
,exchangerate FLOAT NULL
,xpd_onboardinglaunched NVARCHAR(50) NULL
,versionnumber INT NULL
,ts_sourceid NVARCHAR(255) NULL
,xpd_startdate NVARCHAR(50) NULL
,ts_numberofcandidates_state INT NULL
,xpd_uniqueid NVARCHAR(50) NULL
,xpd_rolespecagreed NVARCHAR(50) NULL
,xpd_projectfinalcostest NVARCHAR(100) NULL
,xpd_leavername NVARCHAR(50) NULL
,ts_engagementfirmname NVARCHAR(100) NULL
,mpe_assigntoteamidname NVARCHAR(255) NULL
,owningbusinessunitname NVARCHAR(50) NULL
,xpd_positiontitle NVARCHAR(100) NULL
,utcconversiontimezonecode NVARCHAR(255) NULL
,xpd_replacementreason NVARCHAR(100) NULL
,xpd_fppleadyominame NVARCHAR(255) NULL
,xpd_remcoapprovaldate NVARCHAR(50) NULL
,ts_engagementcompletiondate NVARCHAR(50) NULL
,ts_abortreason NVARCHAR(100) NULL
,xpd_decisiontohire NVARCHAR(50) NULL
,xpd_shortlistinterviewscompleted NVARCHAR(50) NULL
,xpd_montaguteammemberyominame NVARCHAR(50) NULL
,modifiedbyyominame NVARCHAR(50) NULL
,mpe_montaguleadname NVARCHAR(50) NULL
,ts_numberofcandidates INT NULL
,owneridyominame NVARCHAR(50) NULL
,mpe_postengagementcontactsensitivityidname NVARCHAR(255) NULL
,owneridtype NVARCHAR(255) NULL
,createdbyname NVARCHAR(50) NULL
,ts_engagementusefocusids NVARCHAR(350) NULL
,modifiedonbehalfbyyominame NVARCHAR(255) NULL
,ts_leadcontactatfirmname NVARCHAR(50) NULL
,xpd_offerdate NVARCHAR(50) NULL
,ts_engagementsectorfocusids NVARCHAR(350) NULL
,modifiedon NVARCHAR(50) NULL
,ts_engagementgeographicfocusids NVARCHAR(500) NULL
,mpe_flowlastrundate NVARCHAR(50) NULL
,xpd_ratingdate NVARCHAR(50) NULL
,mpe_documentsetid NVARCHAR(50) NULL
,mpe_documentsetname NVARCHAR(100) NULL
,ts_engagementsectorfocus NVARCHAR(250) NULL
,mpe_livecontactsensitivityidname NVARCHAR(255) NULL
,createdon DATETIME2 NULL
,xpd_approvercomments NVARCHAR(255) NULL
,overriddencreatedon NVARCHAR(255) NULL
,ts_summaryofengagement NVARCHAR(850) NULL
,stageid NVARCHAR(255) NULL
,createdbyyominame NVARCHAR(50) NULL
,ts_numberofcandidates_date NVARCHAR(50) NULL
,ts_engagementgeographicfocus NVARCHAR(250) NULL
,xpd_projectleadrating NVARCHAR(255) NULL
,modifiedonbehalfbyname NVARCHAR(255) NULL
,ts_name NVARCHAR(100) NULL
,createdonbehalfbyyominame NVARCHAR(255) NULL
,xpd_projectleadcomment NVARCHAR(255) NULL
,ts_engagementfirmstartdate NVARCHAR(50) NULL
,ts_portfoliocompanyname NVARCHAR(100) NULL
,modifiedbyname NVARCHAR(50) NULL
,mpe_assigntoteamidyominame NVARCHAR(255) NULL
,ts_engagementfirmyominame NVARCHAR(255) NULL
,xpd_projectproposedcost NVARCHAR(150) NULL
,processid NVARCHAR(50) NULL
,ts_engagementusefocus NVARCHAR(300) NULL
,ts_document NVARCHAR(350) NULL
,ts_approveryominame NVARCHAR(50) NULL
,xpd_fppleadfeedback NVARCHAR(255) NULL
,ts_recommendationcomment NVARCHAR(250) NULL
,importsequencenumber INT NULL
,ts_portfoliocompanyleadname NVARCHAR(50) NULL
,transactioncurrencyidname NVARCHAR(50) NULL
,createdonbehalfbyname NVARCHAR(255) NULL
,ts_leadcontactatfirmyominame NVARCHAR(50) NULL
,xpd_fppleadname NVARCHAR(255) NULL
,xpd_currentstage NVARCHAR(255) NULL
,mpe_montaguleadyominame NVARCHAR(50) NULL
,xpd_decisiontoreplace NVARCHAR(50) NULL
,ts_portfoliocompanyleadyominame NVARCHAR(50) NULL
,xpd_hrpackage NVARCHAR(255) NULL
,owneridname NVARCHAR(50) NULL
,mpe_assigntoteamtrigger NVARCHAR(255) NULL
,ts_engagementid NVARCHAR(50) NULL
,xpd_montaguteammembername NVARCHAR(50) NULL
,xpd_contractsigned NVARCHAR(50) NULL
,xpd_talentbriefing NVARCHAR(50) NULL
,ts_portfoliocompanyyominame NVARCHAR(255) NULL
,ts_paymentterms NVARCHAR(250) NULL
,xpd_paymentterms NVARCHAR(650) NULL
,xpd_portfoliocontactfeedback NVARCHAR(255) NULL
,ts_postengagementreport NVARCHAR(2800) NULL
,timezoneruleversionnumber INT NULL
,ts_jobspecavailable NVARCHAR(250) NULL
,traversedpath NVARCHAR(255) NULL
,ts_package NVARCHAR(250) NULL
,xpd_referencingcompleted NVARCHAR(50) NULL
,ts_approvername NVARCHAR(50) NULL
)
GO
Any thoughts
November 30, 2022 at 3:45 pm
The error is self explanatory, there are 2 or more columns with the same name in the base tables.
From what I can see there are a number of duplicate columns, ID, SinkCreatedOn, SinkModifiedOn etc.
You will need to get specific and expand out the * and list the columns you want in the view explicitly, and any duplicate column names, you need to Alias then as a different name.
Additionally as a best practise the use of * is something usually frowned upon, and for best practise DB design you should only be pulling in the columns you specifically care about.
November 30, 2022 at 3:49 pm
You have two tables with the ID column in them. The SELECT * will return both IDs and you can't have the same column name twice in a view. You need to specify every column with the correct table alias and create an alternate name for at least one of the two ID columns.
--Jeff Moden
Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply