Error: Column names in each view or function must be unique

  • 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

     

     

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    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.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply