运行查询的错误消息-列名称或提供值的数量与表定义不匹配

Question

我正在尝试更新一个存储过程，并在代码中添加了u.DIVISION，但是我没有收到错误消息。

列名或提供的值数与表定义不匹配。

对我可能错过了什么有什么建议吗？

SET ansi_nulls ON 
go 
SET quoted_identifier ON 
go 

ALTER PROCEDURE [dbo].[Slx_activity] 
AS 
    DECLARE @ReportStartDate DATETIME, 
            @ReportEndDate   DATETIME 
    DECLARE @Today DATETIME 

    SET @Today = Getdate() 

    DECLARE @Diff INT 

    SET @Diff = Datediff(day, Dateadd(day, 5, 0), @Today) 
    SET @ReportEndDate = Dateadd(day, @Diff - @Diff % 7, Dateadd(day, 5, 0)) 
    SET @ReportStartDate = Datediff(day, 6, @ReportEndDate) 

    IF Object_id('SLXActivity', 'U') IS NOT NULL 
      DROP TABLE slxactivity; 

    SELECT ' Current Activity:' + ' ' + p.[text] AS [TYPE], 
           h.createdate, 
           p.[text], 
           h.[description], 
           h.startdate, 
           Cast (NULL AS DATETIME)               AS COMPLETEDDATE, 
           NULL                                  AS BIDNUMBER, 
           NULL                                  AS BIDSTATUS, 
           h.userid, 
           h.accountid, 
           h.contactid, 
           h.opportunityid 
    INTO   slxactivity 
    FROM   [SalesLogix_Production].[sysdba].[activity] h 
           LEFT JOIN [SalesLogix_Production].[sysdba].[picklist] p 
                  ON h.[type] = p.id 
    WHERE  p.[text] NOT IN ( 'Personal Activity' ) 
           AND Cast(h.startdate AS DATE) BETWEEN 
               @ReportStartDate AND @ReportEndDate 

    INSERT INTO slxactivity 
    SELECT ' Completed Activity:' + ' ' + p.[text] AS [TYPE], 
           h.createdate, 
           p.[text], 
           h.[description], 
           h.startdate, 
           h.completeddate, 
           NULL                                    AS BIDNUMBER, 
           NULL                                    AS BIDSTATUS, 
           h.userid, 
           h.accountid, 
           h.contactid, 
           h.opportunityid 
    FROM   (SELECT accountid, 
                   [description], 
                   Min(historyid) AS HISTORYID, 
                   completeddate 
            FROM   [SalesLogix_Production].[sysdba].[history] 
            GROUP  BY accountid, 
                      [description], 
                      completeddate) a1 
           LEFT JOIN [SalesLogix_Production].[sysdba].[history] h 
                  ON a1.accountid = h.accountid 
                     AND a1.historyid = h.historyid 
           LEFT JOIN [SalesLogix_Production].[sysdba].[picklist] p 
                  ON h.[type] = p.id 
    WHERE  p.[text] NOT IN ( 'Personal Activity' ) 
           AND Cast(h.completeddate AS DATE) BETWEEN 
               @ReportStartDate AND @ReportEndDate 

    INSERT INTO slxactivity 
    SELECT 'Opportunity Added' AS [TYPE], 
           o.createdate, 
           o.status            AS [TEXT], 
           NULL                AS [DESCRIPTION], 
           o.estimatedclose    AS STARTDATE, 
           NULL                AS COMPLETEDDATE, 
           NULL                AS BIDNUMBER, 
           NULL                AS BIDSTATUS, 
           o.createuser, 
           o.accountid, 
           NULL                AS CONTACTID, 
           o.opportunityid 
    FROM   [SalesLogix_Production].[sysdba].[opportunity] o 
    WHERE  Cast(o.createdate AS DATE) BETWEEN 
           @ReportStartDate AND @ReportEndDate 

    INSERT INTO slxactivity 
    SELECT 'Contact Added' AS [TYPE], 
           c.createdate, 
           NULL            AS [TEXT], 
           NULL            AS [DESCRIPTION], 
           NULL            AS STARTDATE, 
           NULL            AS COMPLETEDDATE, 
           NULL            AS BIDNUMBER_STATIC, 
           NULL            AS BID_STATUS, 
           c.createuser, 
           c.accountid, 
           c.contactid, 
           u.division, 
           NULL            AS OPPORTUNITYID 
    FROM   [SalesLogix_Production].[sysdba].[contact] c 
           LEFT JOIN [SalesLogix_Production].[sysdba].[userinfo] u 
                  ON c.createuser = u.userid 
           LEFT JOIN [SalesLogix_Production].[sysdba].[usersecurity] us 
                  ON u.userid = us.userid 
    WHERE  Cast(c.createdate AS DATE) BETWEEN 
           @ReportStartDate AND @ReportEndDate 

    INSERT INTO slxactivity 
    SELECT 'Account Created' AS [TYPE], 
           a.createdate, 
           a.type            AS [TEXT], 
           a.industry        AS [DESCRIPTION], 
           NULL              AS STARTDATE, 
           NULL              AS COMPLETEDDATE, 
           NULL              AS BIDNUMBER_STATIC, 
           NULL              AS BID_STATUS, 
           a.createuser, 
           a.accountid, 
           NULL              AS CONTACTID, 
           NULL              AS OPPORTUNITYID 
    FROM   [SalesLogix_Production].[sysdba].[account] a 
    WHERE  Cast(a.createdate AS DATE) BETWEEN 
           @ReportStartDate AND @ReportEndDate 

    SELECT t.[type], 
           t.createdate, 
           t.[text], 
           u.username, 
           a.account, 
           c.lastname + ', ' + c.firstname AS [CONTACT], 
           o.[description]                 AS OPPORTUNITY, 
           oe.bidnumber_static, 
           oe.bid_status, 
           t.[description], 
           t.startdate, 
           t.completeddate, 
           u.division, 
           @ReportStartDate                AS ReportStartDate, 
           @ReportEndDate                  AS ReportEndDate 
    FROM   [SalesLogix_Production].[sysdba].[userinfo] u 
           JOIN [SalesLogix_Production].[sysdba].[usersecurity] us 
             ON u.userid = us.userid 
           LEFT JOIN slxactivity t 
                  ON u.userid = t.userid 
           LEFT JOIN [SalesLogix_Production].[sysdba].[account] a 
                  ON t.accountid = a.accountid 
           LEFT JOIN [SalesLogix_Production].[sysdba].[contact] c 
                  ON t.contactid = c.contactid 
           LEFT JOIN [SalesLogix_Production].[sysdba].[opportunity] o 
                  ON t.opportunityid = o.opportunityid 
           LEFT JOIN [SalesLogix_Production].[sysdba].[c_opportunity_ext] oe 
                  ON t.opportunityid = oe.opportunityid 
    WHERE  us.[type] NOT IN ( 'R', 'W' ) 
           AND u.username <> 'svc_slxadmin' 

Answer 1

您需要在第一个语句中指定列，该语句创建表

SELECT ' Current Activity:' + ' ' + p.[text] AS [TYPE], 
           h.createdate, 
           p.[text], 
           h.[description], 
           h.startdate, 
           Cast (NULL AS DATETIME)               AS COMPLETEDDATE, 
           NULL                                  AS BIDNUMBER, 
           NULL                                  AS BIDSTATUS, 
           h.userid, 
           h.accountid, 
           h.contactid, 
           CAST(NULL AS VARCHAR(100)) AS division,
           /*
             you need to specify/select the column here, with the relevant
             type, in this context. I assumed it's a string just for the example.
           */
           h.opportunityid 
    INTO   slxactivity 
    FROM   [SalesLogix_Production].[sysdba].[activity] h 
           LEFT JOIN [SalesLogix_Production].[sysdba].[picklist] p 
                  ON h.[type] = p.id 
    WHERE  p.[text] NOT IN ( 'Personal Activity' ) 
           AND Cast(h.startdate AS DATE) BETWEEN 
               @ReportStartDate AND @ReportEndDate