NicolasGutierrezToD提出的问题 -dba

NicolasGutierrezToD

Asked: 2019-08-30 17:33:00 +0800 CST

操作数类型冲突：int 与 datetime2 不兼容

-3

下面的代码会生成此错误：

错误

消息 206，级别 16，状态 2，过程 usp_TRAC_PSE_Step_12_Resource_Request_OBJECT-Production-Part1，第 93 行操作数类型冲突：int 与 datetime2 不兼容

代码

insert into [dbo].[pse__Resource_Request__c_LOAD]
    (OwnerId, pse__End_Date__c, pse__Milestone__c, pse__Percent_Allocated__c, pse__Planned_Bill_Rate__c,
     pse__Practice__c, pse__Project__c, pse__Region__c, pse__SOW_Hours__c, pse__Request_Priority__c,
     pse__Resource_Request_Name__c, pse__Resource_Role__c, pse__Start_Date__c, pse__Status__c,
     Partner_Requested__c, OA_Migration_ID__c, CurrencyIsoCode, Percent_Onsite__c)
    
    select (select [OwnerId] from [dbo].[pse__Proj__c_LOAD] where [OA_Migration_ID__c] = p.id) 'OwnerId'
          , case when case when p.custom_35 is null then ISNULL(b.startdate, '01/01/1900 23:59:59.999') + 180 else p.custom_35 end  <  ISNULL(b.startdate, '01/01/1900 23:59:59.999')  then  ISNULL(b.startdate, '01/01/1900 23:59:59.999') + 180 else case when p.custom_35 is null then ISNULL(b.startdate, '01/01/1900 23:59:59.999') + 180 else p.custom_35 end end 'pse__End_Date__c'
          , null 'pse__Milestone__c' -- Need to figure out how to link to a milestone
          , null 'pse__Percent_Allocated__c'  -- this data is not a percentage.... either remove or data needs to be fixed
          , null 'pse__Planned_Bill_Rate__c' -- Determine if we are loading anything here
          , 'a94300000008OQEAA2'  'pse__Practice__c'  --Not sure where i can get this... talk to xxxx
          , (select [id] from [dbo].[pse__Proj__c_LOAD] where [OA_Migration_ID__c] = p.id) 'pse__Project__c'
          , (select [pse__Region__c] from [dbo].[pse__Proj__c_LOAD] where [OA_Migration_ID__c] = p.id) 'pse__Region__c'
          , b.hours 'pse__SOW_Hours__c'
          , case when p.project_stage_id = 8 then 'High' else 'Normal' End  'pse__Request_Priority__c' -- this column does not exist in the booking table as noted in the mapping document b.priority
          , substring(b.name,0,255) 'pse__Resource_Request_Name__c'
          , case when b.job_code_id = 54 then 'MC - Account Director'
                    when b.job_code_id = 19 then 'MC - Account Director'
                    when b.job_code_id = 23 then 'MC - Technology Architect'
                    when b.job_code_id = 56 then 'MC - Analytics Consultant'
                    when b.job_code_id = 41 then 'MC - Campaign Manager'
                    when b.job_code_id = 21 then 'MC - Centric Development'
                    when b.job_code_id = 17 then 'MC - Client Training Specialist'
                    when b.job_code_id = 50 then 'MC - Designer'
                    when b.job_code_id = 22 then 'MC - Technology Architect'
                    when b.job_code_id = 36 then 'MC - Data Architect'
                    when b.job_code_id = 37 then 'MC - Data Architect'
                    when b.job_code_id = 55 then 'MC - Deliverability Consultant'
                    when b.job_code_id = 7 then 'MC - Deployment Specialist'
                    when b.job_code_id = 15 then 'MC - Designer'
                    when b.job_code_id = 27 then 'MC - Technology Architect'
                    when b.job_code_id = 45 then 'MC - Project Manager'
                    when b.job_code_id = 42 then 'MC - Project Manager'
                    when b.job_code_id = 44 then 'MC - Project Manager'
                    when b.job_code_id = 46 then 'MC - Project Manager'
                    when b.job_code_id = 49 then 'MC - Project Manager'
                    when b.job_code_id = 43 then 'MC - Project Manager'
                    when b.job_code_id = 11 then 'MC - Deployment Specialist'
                    when b.job_code_id = 10 then 'MC - Deployment Specialist'
                    when b.job_code_id = 20 then 'MC - Technology Architect'
                    when b.job_code_id = 25 then 'MC - Technology Architect'
                    when b.job_code_id = 18 then 'MC - Project Manager'
                    when b.job_code_id = 26 then 'MC - Marketing Consultant'
                    when b.job_code_id = 9 then 'MC - Marketing Consultant'
                    when b.job_code_id = 31 then 'MC - Deployment Specialist'
                    when b.job_code_id = 16 then 'MC - Deployment Specialist'
                    when b.job_code_id = 35 then 'MC - Project Manager'
                    when b.job_code_id = 53 then 'MC - Marketing Consultant'
                    when b.job_code_id = 39 then 'MC - Production Specialist'
                    when b.job_code_id = 5 then 'MC - Program Manager'
                    when b.job_code_id = 3 then 'MC - Program Manager'
                    when b.job_code_id = 4 then 'MC - Program Manager'
                    when b.job_code_id = 2 then 'MC - Project Manager'
                    when b.job_code_id = 1 then 'MC - Project Manager'
                    when b.job_code_id = 47 then 'MC - Deployment Specialist'
                    when b.job_code_id = 14 then 'MC - Designer'
                    when b.job_code_id = 8 then 'MC - Marketing Consultant'
                    when b.job_code_id = 48 then 'MC - Project Manager'
                    when b.job_code_id = 24 then 'MC - Project Manager'
                    when b.job_code_id = 38 then 'MC - Designer'
                    when b.job_code_id = 52 then 'MC - Marketing Consultant'
                    when b.job_code_id = 40 then 'MC - Solution Architect'
                    when b.job_code_id = 51 then 'MC - Project Manager'
                    when b.job_code_id = 6 then 'MC - Technical Producer'
                    when b.job_code_id = 57 then 'MC - Technical QA Specialist'
                    when b.job_code_id = 12 then 'MC - Technology Architect'
                    when b.job_code_id = 13 then 'MC - Technology Specialist'
                        else case when b.job_code_id = 0 then case when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 54 then 'MC - Account Director'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id) = 19 then 'MC - Account Director'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 23 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 56 then 'MC - Analytics Consultant'
                    when(select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 41 then 'MC - Campaign Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 21 then 'MC - Centric Development'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 17 then 'MC - Client Training Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 50 then 'MC - Designer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 22 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 36 then 'MC - Data Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 37 then 'MC - Data Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 55 then 'MC - Deliverability Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 7 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 15 then 'MC - Designer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 27 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 45 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 42 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 44 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 46 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 49 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 43 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 11 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 10 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 20 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 25 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 18 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 26 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 9 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 31 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 16 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 35 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 53 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 39 then 'MC - Production Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 5 then 'MC - Program Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 3 then 'MC - Program Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 4 then 'MC - Program Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 2 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 1 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 47 then 'MC - Deployment Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 14 then 'MC - Designer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 8 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 48 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 24 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 38 then 'MC - Designer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 52 then 'MC - Marketing Consultant'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 40 then 'MC - Solution Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 51 then 'MC - Project Manager'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 6 then 'MC - Technical Producer'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 57 then 'MC - Technical QA Specialist'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 12 then 'MC - Technology Architect'
                    when (select job_code_id from OpenAir_user iu where iu.id = b.user_id)  = 13 then 'MC - Technology Specialist' end
                         else 'Undefined' end   End 'pse__Resource_Role__c' -- xxxxx is going to send me the mapping file to translate job code to Org62 values
        --  , null 'pse__Staffer_Approval_Required__c' -- need to determine if this is needed
          , b.startdate 'pse__Start_Date__c'
          , case when p.project_stage_id = 8 then 'Ready to Staff' else 'Assigned' End  'pse__Status__c'-- this column does not exist in the booking table as noted in the mapping document b.approval_Status
          , case when custom_64 ='Partner Requested' and p.project_stage_id in (8, 14) then 'true' else 'false' end 'Partner_Requested__c'
          , b.id 'OA_Migration_ID__c'
          , (select CurrencyIsoCode from [dbo].[pse__Proj__c_LOAD] where [OA_Migration_ID__c] = p.id)  'CurrencyIsoCode'
          , 0 'Percent_Onsite__c'
    from openair_integration.dbo.booking b,
         [dbo].[Migrated_Projects] mp,
         openair_integration.dbo.project p
    where b.project_id = mp.id
    and b.project_id = p.id
    and booking_type_id = 8
    --and b.custom_64 <> 'SOW Hours'
    and isnull(b.deleted,0) <> 1
    --and mp.stage = 'Active'

NicolasGutierrezToD

Asked: 2019-08-15 11:05:40 +0800 CST

开头的标识符太长。最大长度为 128

-1

我参考了其他一些博客，我无法将问题隔离到这个逻辑。代码如下。抛出附加的错误消息。

            -- 1.   Import Multiple Delimited Text Files into a SQL Database

            -- 1.1  Define the path to the input and define the terminators


            USE [Openair_Integration]
            DECLARE @Path NVARCHAR(255) = 'C:\\Users\nicolas.gutierrez.su\Downloads\\'
            DECLARE @RowTerminator NVARCHAR(5) = CHAR(13) + CHAR(10)
            DECLARE @ColumnTerminator NVARCHAR(5) = CHAR(9)

            -- 1.2  Define the list of input and output in a temporary table


            IF OBJECT_ID('[dbo].[Files_Temporary]', 'U') IS NOT NULL
            DROP TABLE [dbo].[Files_Temporary];
            CREATE TABLE [dbo].[Files_Temporary]
            (
            [ID] INT
            , [FileName] NVARCHAR(255)
            , [TableName] NVARCHAR(255)
            );

            INSERT INTO [dbo].[Files_Temporary] SELECT 1,   'booking.csv',  'dbo.booking'

            -- 1.3  Loop over the list of input and output and import each file to the correct table


            DECLARE @Counter INT = 1

            WHILE @Counter <= (SELECT COUNT(*) FROM [dbo].[Files_Temporary])
            BEGIN
            PRINT 'Counter is ''' + CONVERT(NVARCHAR(5), @Counter) + '''.'

            DECLARE @FileName NVARCHAR(255)
            DECLARE @TableName NVARCHAR(255)
            DECLARE @Header NVARCHAR(MAX)
            DECLARE @SQL_Header NVARCHAR(MAX)
            DECLARE @CreateHeader NVARCHAR(MAX) = ''
            DECLARE @SQL_CreateHeader NVARCHAR(MAX)

            SELECT @FileName = [FileName], @TableName = [TableName] FROM [dbo].[Files_Temporary] WHERE [ID] = @Counter

            IF OBJECT_ID('[dbo].[' + @TableName + ']', 'U') IS NULL
            BEGIN
        -----------------------------------------------------------------------------------------------------------------------------------------------------------
                PRINT 'Creating new table with name ''' + @TableName + '''.'

                IF OBJECT_ID('[dbo].[Header_Temporary]', 'U') IS NOT NULL
                DROP TABLE [dbo].[Header_Temporary];
                CREATE TABLE [dbo].[Header_Temporary]
                (
                    [Header] NVARCHAR(MAX)
                );

                SET @SQL_Header = '
                    BULK INSERT [dbo].[Header_Temporary]
                    FROM ''' + @Path + @FileName + '''
                    WITH
                    (
                        FIRSTROW = 1,
                        LASTROW = 1,
                        MAXERRORS = 0,
                        FIELDTERMINATOR = ''' + @RowTerminator + ''',
                        ROWTERMINATOR = ''' + @RowTerminator + '''
                    )'
                EXEC(@SQL_Header)

                SET @Header = (SELECT TOP 1 [Header] FROM [dbo].[Header_Temporary])
                PRINT 'Extracted header ''' + @Header + ''' for table ''' + @TableName + '''.'
        ----------------------------------------------------------------------------------------------------------------------------------------------------------------
                WHILE CHARINDEX(@ColumnTerminator, @Header) > 0
                BEGIN          
                    SET @CreateHeader = @CreateHeader + '[' + LTRIM(RTRIM(SUBSTRING(@Header, 1, CHARINDEX(@ColumnTerminator, @Header) - 1))) + '] NVARCHAR(255), '
                    SET @Header = SUBSTRING(@Header, CHARINDEX(@ColumnTerminator, @Header) + 1, LEN(@Header)) 
                END
                SET @CreateHeader = @CreateHeader + '[' + @Header + '] NVARCHAR(255)'

                SET @SQL_CreateHeader = 'CREATE TABLE [' + @TableName + '] (' + @CreateHeader + ')'
                EXEC(@SQL_CreateHeader)
            END

            -------------------------------------------------------------------------------------------------------------------------------------------------------------
            PRINT 'Inserting data from ''' + @FileName + ''' to ''' + @TableName + '''.'
            DECLARE @SQL NVARCHAR(MAX)
            SET @SQL = '
                BULK INSERT [dbo].[' + @TableName + ']
                FROM ''' + @Path + @FileName + '''
                WITH
                (
                    FIRSTROW = 2,
                    MAXERRORS = 0,
                    FIELDTERMINATOR = ''' + @ColumnTerminator + ''',
                    ROWTERMINATOR = ''' + @RowTerminator + '''
                )'
            EXEC(@SQL)

            SET @Counter = @Counter + 1
            END;

            -- 1.4  Cleanup temporary tables


            IF OBJECT_ID('[dbo].[Files_Temporary]', 'U') IS NOT NULL
            DROP TABLE [dbo].[Files_Temporary];

            IF OBJECT_ID('[dbo].[Header_Temporary]', 'U') IS NOT NULL
            DROP TABLE [dbo].[Header_Temporary];

操作数类型冲突：int 与 datetime2 不兼容

错误

代码

开头的标识符太长。最大长度为 128

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

NicolasGutierrezToD's questions

错误

代码