mysql - 从 MySQL 迁移到 SQL Server 时出现复杂查询的意外结果？

Question

最近，我们正在将项目从 MySQL 迁移到 SQL Server。

我们使用了工具SSMA - SQL Server Migration Assistant。

我们已完全恢复数据。然而，之后进行了一些更改。

SQL 存储过程出现问题。

在 MySQL 中:

CREATE DEFINER=`root`@`localhost` PROCEDURE
 `generate_file_outward`(IN presentday int)
BEGIN

select 67,destination_bank_ifsc,party_account_type,
    case ecs_account_type  when 1 then 'LON' when 2 
then 'REC' when 3 then 'LOK' when 4 then 'DEM'
 when 5 then 'MSI' else 'BGL' END as ecs_account_type,
    party_account_no,left(party_account_name,40) as party_account_name,'XXXXXXXX',requestor_id,sponsor_bank_name1,
id,mandate_request_id,mandate_amount,umrn_no from outward_master
where checker_flag=1 and status_flag=1 and 
umrn_no is not null and return_flag=0 and   requestor_id in ('XXXX00004000006207','XXXX00000000022678','XXXX00000000022702') 
and (to_date >= from_date  OR DATE(to_date)='0000-00-00') 
    AND (DATE(to_date)>=DATE(NOW()) 
OR DATE(to_date)='0000-00-00') and 
DATE(from_date)<=DATE(NOW()) and ecs_day=presentday and 
month(now())= if(mandate_frequency='YEAR',month(from_date+INTERVAL 12 MONTH),
    if(mandate_frequency='QURT',month(from_date+INTERVAL 3 MONTH),month(now())));
END

然而，在SQL Server中却失败了。

我们一直在尝试该查询，但无法在 SQL Server 中生成相同的结果。

SSMA 生成了一些函数并将查询转换为这样

USE [files]
GO
/****** Object:  StoredProcedure [files].[generate_file_outward]    Script Date: 10/24/2019 11:33:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
*   SSMA informational messages:
*   M2SS0003: The following SQL clause was ignored during conversion:
*   DEFINER = `root`@`localhost`.
*/

ALTER PROCEDURE [files].[generate_file_outward]  
   @presentday int
AS 
   BEGIN

      SET  XACT_ABORT  ON

      SET  NOCOUNT  ON

      /*
      *   SSMA warning messages:
      *   M2SS0219: Converted operator may not work exactly the same as in MySQL

      *   SSMA informational messages:
      *   M2SS0052: string literal was converted to NUMERIC literal
      *   M2SS0052: string literal was converted to NUMERIC literal
      */

      SELECT 
         67, 
         outward_master.destination_bank_ifsc, 
         outward_master.party_account_type, 
         CASE outward_master.ecs_account_type
            WHEN 1 THEN 'LON'
            WHEN 2 THEN 'REC'
            WHEN 3 THEN 'LOK'
            WHEN 4 THEN 'DEM'
            WHEN 5 THEN 'MSI'
            ELSE N'BGL'
         END AS ecs_account_type, 
         outward_master.party_account_no, 
         left(outward_master.party_account_name, 40) AS party_account_name, 
         'xxxxxxxxxxx', 
         outward_master.requestor_id, 
         outward_master.sponsor_bank_name1, 
         outward_master.ID, 
         outward_master.mandate_request_id, 
         outward_master.mandate_amount, 
         outward_master.umrn_no
      FROM files.outward_master
      WHERE 
         outward_master.checker_flag = 1 AND 
         outward_master.status_flag = 1 AND 
         outward_master.umrn_no IS NOT NULL AND 
         outward_master.return_flag = 0 AND 
         outward_master.requestor_id IN ( 'xxxx00004000006207', 'xxxx00000000022678', 'xxxx00000000022702' ) AND 
         (outward_master.to_date >= outward_master.from_date OR CAST(CONVERT(varchar(10), outward_master.to_date, 112) AS int) = 0000) AND 
         (outward_master.to_date >= getdate() OR CAST(CONVERT(varchar(10), outward_master.to_date, 112) AS int) = 0000) AND 
         outward_master.from_date <= getdate() AND 
         outward_master.ecs_day = @presentday AND 
         datepart(MONTH, getdate()) = 
         CASE 
            WHEN (outward_master.mandate_frequency = 'YEAR') THEN datepart(MONTH, m2ss.num_to_date((CAST(CONVERT(varchar(10), outward_master.from_date, 112) AS int) + NULL)))
            ELSE 
               CASE 
                  WHEN (outward_master.mandate_frequency = 'QURT') THEN datepart(MONTH, m2ss.num_to_date((CAST(CONVERT(varchar(10), outward_master.from_date, 112) AS int) + NULL)))
                  ELSE datepart(MONTH, getdate())
               END
         END

   END

self 功能:

USE [files]
GO
/****** Object:  UserDefinedFunction [m2ss].[num_to_date]    Script Date: 10/25/2019 12:06:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [m2ss].[num_to_date](@val NUMERIC(38,0)) RETURNS DATE
AS
  BEGIN
    RETURN cast(m2ss.num_to_datetime(@val) AS DATE)
  END

我们无法得出与 MySQL 中的查询相同的结果。

可能出了什么问题？

这是我们的示例数据

https://ibb.co/y5G1XTY

内涵:

ADHO - As and When Presented, YEAR = Yearly, MNTH = Monthly, QURT = Quarterly

我们的记录生成日期是 (4,7,10,15,22,28)，因此输入是当前日期。

我们已经在 MySQL 中构建了一个查询，但是在 SQL Server 中无法有效工作，因为知道这两种数据库语法完全不同，有什么建议吗？

Answer 1

SELECT     destination_bank_ifsc, party_account_type, 
                      CASE ecs_account_type WHEN 1 THEN 'LON' WHEN 2 THEN 'REC' WHEN 3 THEN 'LOK' WHEN 4 THEN 'DEM' WHEN 5 THEN 'MSI' ELSE 'BGL' END AS ecs_account_type,
                       party_account_no, LEFT(party_account_name, 40) AS party_account_name, requestor_id, sponsor_bank_name1, ID, mandate_request_id, mandate_amount, 
                      umrn_no
FROM         outward_master
WHERE     (checker_flag = 1) AND (status_flag = 1) AND (umrn_no IS NOT NULL) AND (return_flag = 0) AND (requestor_id IN ('xxxxxxxxxx', 'xxxxxxxxxx', 
                      'xxxxxxxxx')) AND (to_date >= from_date OR
                      to_date IS NULL) AND (to_date >= CONVERT(date, GETDATE()) OR
                      to_date IS NULL) AND (from_date <= CONVERT(date, GETDATE())) AND (ecs_day = 4) AND (DATEPART(month, GETDATE()) 
                      = CASE WHEN mandate_frequency = 'YEAR' THEN DATEPART(month, CONVERT(date, DATEADD(month, 12, from_date))) 
                      WHEN mandate_frequency = 'QURT' THEN DATEPART(month, CONVERT(date, DATEADD(month, 3, from_date))) ELSE DATEPART(month, getdate()) END)

学习一下很好，而且所有rdms数据库语法都不同。