时间戳

首页 > 解决方案 > 检测昨天负载和今天负载之间差异但填充错误的查询

sql - 检测昨天负载和今天负载之间差异但填充错误的查询

问题描述

我们正在转换旧的遗留程序并将其集成到 SSIS 中。旧程序向需要通知帐户更改的最终用户输出一个显示帐户差异的文件。这是我试图执行此操作的查询，但我收到错误“varchar 值'02393224650'的转换溢出了一个 int 列。” 在你说“可能重复”之前。其他帖子都没有正确回答这个问题，也没有涉及以这种方式实现逻辑的查询。


      SELECT
      a.run_id,
      a.AccountNumber,
      a.AccountBranch,
      a.AccountName,
      a.AccountType,
      a.CostCenter,
      b.TransactionLimit,
      b.ClientName,
      b.DailyCumulativeLimit
    FROM 
      AEI_GFXAccounts a
      LEFT JOIN AEI_GFXAccountSTP as b
      ON 
        a.feed_id = b.feed_id and 
        a.run_id = b.run_id and a.GFXAccountsID = b.GFXAccountSTPID), 
yest AS (
  SELECT * FROM data WHERE run_id = (SELECT MAX(run_id)-1 FROM AEI_GFXAccounts)
),
toda AS (
  SELECT * FROM data WHERE run_id = (SELECT MAX(run_id) FROM AEI_GFXAccounts)
)

SELECT
  CASE WHEN COALESCE(yest.AccountNumber, 0) <> COALESCE(toda.AccountNumber, 0) THEN yest.AccountNumber END as yest_AccountNumber,
  CASE WHEN COALESCE(yest.AccountNumber, 0) <> COALESCE(toda.AccountNumber, 0) THEN toda.AccountNumber END as toda_AccountNumber,
  CASE WHEN COALESCE(yest.AccountBranch, 0) <> COALESCE(toda.AccountBranch, 0) THEN yest.AccountBranch END as yest_AccountBranch,
  CASE WHEN COALESCE(yest.AccountBranch, 0) <> COALESCE(toda.AccountBranch, 0) THEN toda.AccountBranch END as toda_AccountBranch,
  CASE WHEN COALESCE(yest.AccountName, 'x')   <> COALESCE(toda.AccountName, 'x')   THEN yest.AccountName   END as yest_AccountName,
  CASE WHEN COALESCE(yest.AccountName, 'x')   <> COALESCE(toda.AccountName, 'x')   THEN toda.AccountName   END as toda_AccountName,
  CASE WHEN COALESCE(yest.AccountType, 'n/a')   <> COALESCE(toda.AccountType, 'n/a')   THEN yest.AccountType   END as yest_AccountType,
  CASE WHEN COALESCE(yest.AccountType, 'n/a')   <> COALESCE(toda.AccountType, 'n/a')   THEN toda.AccountType   END as toda_AccountType,
  CASE WHEN COALESCE(yest.CostCenter, 0)   <> COALESCE(toda.CostCenter, 0)   THEN yest.CostCenter   END as yest_CostCenter,
  CASE WHEN COALESCE(yest.CostCenter, 0)   <> COALESCE(toda.CostCenter, 0)   THEN toda.CostCenter  END as toda_CostCenter,
  CASE WHEN COALESCE(yest.TransactionLimit, 0)   <> COALESCE(toda.TransactionLimit, 0)   THEN yest.TransactionLimit   END as yest_TransactionLimit,
  CASE WHEN COALESCE(yest.TransactionLimit, 0)   <> COALESCE(toda.TransactionLimit, 0)   THEN toda.TransactionLimit  END as toda_TransactionLimit,
  CASE WHEN COALESCE(yest.ClientName, 'x')   <> COALESCE(toda.ClientName, 'x')   THEN yest.ClientName   END as yest_ClientName,
  CASE WHEN COALESCE(yest.ClientName, 'x')   <> COALESCE(toda.ClientName, 'x')   THEN toda.ClientName  END as toda_ClientName,
  CASE WHEN COALESCE(yest.DailyCumulativeLimit, 0)   <> COALESCE(toda.DailyCumulativeLimit, 0)   THEN yest.DailyCumulativeLimit   END as yest_DailyCumulativeLimit,
  CASE WHEN COALESCE(yest.DailyCumulativeLimit, 0)   <> COALESCE(toda.DailyCumulativeLimit, 0)   THEN toda.DailyCumulativeLimit  END as toda_DailyCumulativeLimit

FROM
  toda LEFT JOIN yest ON toda.accountNumber = yest.AccountNumber

WHERE
  COALESCE(toda.AccountNumber,   0) <> COALESCE(yest.AccountNumber,   0) OR 
  COALESCE(toda.AccountBranch, 0) <> COALESCE(yest.AccountBranch, 0) OR      
  COALESCE(toda.AccountName,   'x') <> COALESCE(yest.AccountName,   'x') OR 
  COALESCE(toda.AccountType,   'n/a') <> COALESCE(yest.AccountType,   'n/a') OR 
  COALESCE(toda.CostCenter,   0) <> COALESCE(yest.CostCenter,   0) OR 
  COALESCE(toda.TransactionLimit,   0) <> COALESCE(yest.TransactionLimit,   0) OR 
  COALESCE(toda.ClientName,   'x') <> COALESCE(yest.ClientName,   'x') OR
  COALESCE(toda.DailyCumulativeLimit,   0) <> COALESCE(yest.DailyCumulativeLimit,   0)


      SELECT
      a.run_id,
      a.AccountNumber,
      a.AccountBranch,
      a.AccountName,
      a.AccountType,
      a.CostCenter,
      b.TransactionLimit,
      b.ClientName,
      b.DailyCumulativeLimit
    FROM 
      AEI_GFXAccounts a
      LEFT JOIN AEI_GFXAccountSTP as b
      ON 
        a.feed_id = b.feed_id and 
        a.run_id = b.run_id and a.GFXAccountsID = b.GFXAccountSTPID

我希望它返回一个仅显示所有已更改帐户的视图/查询结果。使用不依赖于填充 n/a 数据的字段。但我却得到了错误：

消息 248，第 16 层，状态 1，第 5 行

varchar 值 '02393224650' 的转换溢出了一个 int 列。

这对我来说没有意义，因为该表已经支持已经存储在表中的该卷的 int 数。

标签： sqlsql-serverssis

解决方案

我找到了答案。问题是该表的设计数据类型实际上是一个 VarChar。COALESCE 正在尝试输入一个 int。当它最终是一个varchar时。

推荐阅读