当前位置： 文江博客话题详情

突触专用池中的动态SQL失败，但非动态查询作品可行

发布于 2025-01-30 09:01:03 字数 6138 浏览 3 评论 0原文

我通过执行SP_EXECUTESQL @SQL;通过Azure Synapse专用SQL池中的SSMS执行的下面动态SQL查询，返回随机语法错误，例如

在行划分错误：105，列：44：“ log_case”附近的语法不正确。

但是，如果我在SSM中执行此查询的非动态版本，则可以正常工作。我考虑/尝试的事情而没有成功：

  • 找到具有记事本++检查长度的特殊字符或非窗口线结尾
  • 低于8000（Nvarchar的最大值）

有人知道我的动态查询有什么问题，为什么它失败了？这让我头疼。

DECLARE @sql nvarchar(max);
DECLARE @clientstaging nvarchar(50) = 'staging';

SET @SQL = 'insert into ' + @clientstaging + '.log_CaseSLAChanges (
    id,
    caseDocumentType,
    clientOrganisation,
    billDate,
    billNumber,
    serviceDescription,
    positionChanged,
    servicePeriod,
    currency,
    prices,
    totalPrice,
    netPrice,
    vatPrice,
    vatRate,
    vatNr,
    taxNr,
    creditor
)

SELECT
        c.id id,
        t.title,
        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''clientOrganisation'') > 0
            THEN 1
        ELSE 0 END "clientOrganisation",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billDate'') > 0
            THEN 1
        ELSE 0 END "billDate",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billNumber'') > 0
            THEN 1
        ELSE 0 END "billNumber",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''serviceDescription'') > 0
            THEN 1
        ELSE 0 END "serviceDescription",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND ((l.fieldKey = ''Position'') OR (
                       l.fieldKey in (''description'', ''amount'', ''unit'', ''unitPrice'', ''totalPrice'', ''vatPrice'', ''vatRate'')
                       AND l.typeID = 1))) > 0
            THEN 1
        ELSE 0 END "positionChanged",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''servicePeriod'') > 0
            THEN 1
        ELSE 0 END "servicePeriod",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''currency'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "currency",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey IN (''totalPrice'', ''netPrice'', ''vatPrice'', ''vatRate'') AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "prices",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''totalPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "totalPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''netPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "netPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatRate'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatRate",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatNr'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatNr",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''taxNr'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "taxNr",

        CASE WHEN (select COUNT(*)
                   FROM ' + @clientstaging + '.backend_CaseData d
                       JOIN ' + @clientstaging + '.backend_CaseCreditorData cdc on d.capturedCaseDataCreditorID = cdc.id
                       JOIN ' + @clientstaging + '.backend_CaseCreditorData cdd on d.caseCreditorDataID = cdd.id
                   WHERE d.caseID = c.id AND (cdc.normalizedReducedName != cdd.normalizedReducedName OR
                                              cdc.normalizedStreetAndNumber != cdd.normalizedStreetAndNumber)
                  ) > 0
            THEN 1
        ELSE 0 END "creditor"

FROM ' + @clientstaging + '.backend_Case c
        JOIN ' + @clientstaging + '.backend_CaseProcess p ON p.caseID = c.id
        JOIN ' + @clientstaging + '.backend_CaseData d on c.id = d.caseID
        JOIN ' + @clientstaging + '.md_CaseDocumentType t ON t.id = d.caseDocumentTypeID
WHERE c.uploadedByFastLane = 0
  AND p.processStepID != 0
  and c.deleted = 0
  and c.archived = 1';
EXECUTE sp_executesql @sql;
原文

The below dynamic SQL query, which I execute via EXECUTE sp_executesql @sql; through SSMS in an Azure Synapse dedicated SQL Pool, returns random syntax errors, such as

Parse error at line: 105, column: 44: Incorrect syntax near 'log_Case'.

However, if I execute a non-dynamic version of this query in SSMS it works just fine. Things that I considered/tried without success:

  • Find special characters or non-windows line endings with Notepad++
  • Check length of query is below 8000 (max of nvarchar)

Anyone has an idea what is wrong with my dynamic query and why it fails? This gives me a headache.

DECLARE @sql nvarchar(max);
DECLARE @clientstaging nvarchar(50) = 'staging';

SET @SQL = 'insert into ' + @clientstaging + '.log_CaseSLAChanges (
    id,
    caseDocumentType,
    clientOrganisation,
    billDate,
    billNumber,
    serviceDescription,
    positionChanged,
    servicePeriod,
    currency,
    prices,
    totalPrice,
    netPrice,
    vatPrice,
    vatRate,
    vatNr,
    taxNr,
    creditor
)

SELECT
        c.id id,
        t.title,
        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''clientOrganisation'') > 0
            THEN 1
        ELSE 0 END "clientOrganisation",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billDate'') > 0
            THEN 1
        ELSE 0 END "billDate",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billNumber'') > 0
            THEN 1
        ELSE 0 END "billNumber",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''serviceDescription'') > 0
            THEN 1
        ELSE 0 END "serviceDescription",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND ((l.fieldKey = ''Position'') OR (
                       l.fieldKey in (''description'', ''amount'', ''unit'', ''unitPrice'', ''totalPrice'', ''vatPrice'', ''vatRate'')
                       AND l.typeID = 1))) > 0
            THEN 1
        ELSE 0 END "positionChanged",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''servicePeriod'') > 0
            THEN 1
        ELSE 0 END "servicePeriod",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''currency'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "currency",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey IN (''totalPrice'', ''netPrice'', ''vatPrice'', ''vatRate'') AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "prices",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''totalPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "totalPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''netPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "netPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatRate'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatRate",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatNr'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatNr",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + @clientstaging + '.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''taxNr'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "taxNr",

        CASE WHEN (select COUNT(*)
                   FROM ' + @clientstaging + '.backend_CaseData d
                       JOIN ' + @clientstaging + '.backend_CaseCreditorData cdc on d.capturedCaseDataCreditorID = cdc.id
                       JOIN ' + @clientstaging + '.backend_CaseCreditorData cdd on d.caseCreditorDataID = cdd.id
                   WHERE d.caseID = c.id AND (cdc.normalizedReducedName != cdd.normalizedReducedName OR
                                              cdc.normalizedStreetAndNumber != cdd.normalizedStreetAndNumber)
                  ) > 0
            THEN 1
        ELSE 0 END "creditor"

FROM ' + @clientstaging + '.backend_Case c
        JOIN ' + @clientstaging + '.backend_CaseProcess p ON p.caseID = c.id
        JOIN ' + @clientstaging + '.backend_CaseData d on c.id = d.caseID
        JOIN ' + @clientstaging + '.md_CaseDocumentType t ON t.id = d.caseDocumentTypeID
WHERE c.uploadedByFastLane = 0
  AND p.processStepID != 0
  and c.deleted = 0
  and c.archived = 1';
EXECUTE sp_executesql @sql;

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

路还长，别太狂 2025-02-06 09:01:03

调试动态SQL的最简单方法是print/首先选择。然后，您可以在将解决方案传播到生成动态语句的SQL之前调试SQL并解决问题。通常，您会发现问题很简单，例如在字面字符串中很难发现的印刷错误；例如，缺少空格/线路破坏或领先/尾随的定界符。花点时间才能获得非动态陈述，这确实很重要，好像这无效，动态的人将没有机会正确地工作。

如果您做了print/选择语句，您会注意到它已被截断为4,000个字符，因为您的文字仅是nvarchar（4000） 。 cast/将其一部分转换为max：

DECLARE @sql nvarchar(max);
DECLARE @clientstaging sysname = N'staging';

SET @SQL = CONVERT(nvarchar(MAX),N'insert into ') + QUOTENAME(@clientstaging) + N'.log_CaseSLAChanges (
    id,
    caseDocumentType,
    clientOrganisation,
    billDate,
    billNumber,
    serviceDescription,
    positionChanged,
    servicePeriod,
    currency,
    prices,
    totalPrice,
    netPrice,
    vatPrice,
    vatRate,
    vatNr,
    taxNr,
    creditor
)

SELECT
        c.id id,
        t.title,
        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''clientOrganisation'') > 0
            THEN 1
        ELSE 0 END "clientOrganisation",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billDate'') > 0
            THEN 1
        ELSE 0 END "billDate",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billNumber'') > 0
            THEN 1
        ELSE 0 END "billNumber",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''serviceDescription'') > 0
            THEN 1
        ELSE 0 END "serviceDescription",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND ((l.fieldKey = ''Position'') OR (
                       l.fieldKey in (''description'', ''amount'', ''unit'', ''unitPrice'', ''totalPrice'', ''vatPrice'', ''vatRate'')
                       AND l.typeID = 1))) > 0
            THEN 1
        ELSE 0 END "positionChanged",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''servicePeriod'') > 0
            THEN 1
        ELSE 0 END "servicePeriod",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''currency'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "currency",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey IN (''totalPrice'', ''netPrice'', ''vatPrice'', ''vatRate'') AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "prices",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''totalPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "totalPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''netPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "netPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatRate'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatRate",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatNr'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatNr",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''taxNr'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "taxNr",

        CASE WHEN (select COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.backend_CaseData d
                       JOIN ' + QUOTENAME(@clientstaging) + N'.backend_CaseCreditorData cdc on d.capturedCaseDataCreditorID = cdc.id
                       JOIN ' + QUOTENAME(@clientstaging) + N'.backend_CaseCreditorData cdd on d.caseCreditorDataID = cdd.id
                   WHERE d.caseID = c.id AND (cdc.normalizedReducedName != cdd.normalizedReducedName OR
                                              cdc.normalizedStreetAndNumber != cdd.normalizedStreetAndNumber)
                  ) > 0
            THEN 1
        ELSE 0 END "creditor"

FROM ' + QUOTENAME(@clientstaging) + N'.backend_Case c
        JOIN ' + QUOTENAME(@clientstaging) + N'.backend_CaseProcess p ON p.caseID = c.id
        JOIN ' + QUOTENAME(@clientstaging) + N'.backend_CaseData d on c.id = d.caseID
        JOIN ' + QUOTENAME(@clientstaging) + N'.md_CaseDocumentType t ON t.id = d.caseDocumentTypeID
WHERE c.uploadedByFastLane = 0
  AND p.processStepID != 0
  and c.deleted = 0
  and c.archived = 1';

EXECUTE sys.sp_executesql @sql;

另外，我怀疑您需要所有这些子征服；他们会为性能糟糕。

The easiest way to debug dynamic SQL is to PRINT/SELECT the statement first. Then you can debug that SQL and solve the problem before propagating the solution to your SQL that generates the dynamic statement. Often you'll find that the problems are quite simple, such as a typographical error that is difficult to spot in the literal strings; for example a missing whitespace/linebreak, or leading/trailing delimiters. Taking the time to get the non-dynamic statement working first is really important, as if that doesn't work the dynamic one will have no chance of working correctly.

If you did PRINT/SELECT the statement, you'd note it was truncated to 4,000 characters as your literal is only an nvarchar(4000). CAST/CONVERT part of it to a MAX:

DECLARE @sql nvarchar(max);
DECLARE @clientstaging sysname = N'staging';

SET @SQL = CONVERT(nvarchar(MAX),N'insert into ') + QUOTENAME(@clientstaging) + N'.log_CaseSLAChanges (
    id,
    caseDocumentType,
    clientOrganisation,
    billDate,
    billNumber,
    serviceDescription,
    positionChanged,
    servicePeriod,
    currency,
    prices,
    totalPrice,
    netPrice,
    vatPrice,
    vatRate,
    vatNr,
    taxNr,
    creditor
)

SELECT
        c.id id,
        t.title,
        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''clientOrganisation'') > 0
            THEN 1
        ELSE 0 END "clientOrganisation",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billDate'') > 0
            THEN 1
        ELSE 0 END "billDate",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billNumber'') > 0
            THEN 1
        ELSE 0 END "billNumber",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''serviceDescription'') > 0
            THEN 1
        ELSE 0 END "serviceDescription",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND ((l.fieldKey = ''Position'') OR (
                       l.fieldKey in (''description'', ''amount'', ''unit'', ''unitPrice'', ''totalPrice'', ''vatPrice'', ''vatRate'')
                       AND l.typeID = 1))) > 0
            THEN 1
        ELSE 0 END "positionChanged",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''servicePeriod'') > 0
            THEN 1
        ELSE 0 END "servicePeriod",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''currency'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "currency",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey IN (''totalPrice'', ''netPrice'', ''vatPrice'', ''vatRate'') AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "prices",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''totalPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "totalPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''netPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "netPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatPrice'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatPrice",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatRate'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatRate",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''vatNr'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "vatNr",

        CASE WHEN (SELECT COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.log_CaseChangesLog l
                   WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
                         l.fieldKey = ''taxNr'' AND l.typeID = 0) > 0
            THEN 1
        ELSE 0 END "taxNr",

        CASE WHEN (select COUNT(*)
                   FROM ' + QUOTENAME(@clientstaging) + N'.backend_CaseData d
                       JOIN ' + QUOTENAME(@clientstaging) + N'.backend_CaseCreditorData cdc on d.capturedCaseDataCreditorID = cdc.id
                       JOIN ' + QUOTENAME(@clientstaging) + N'.backend_CaseCreditorData cdd on d.caseCreditorDataID = cdd.id
                   WHERE d.caseID = c.id AND (cdc.normalizedReducedName != cdd.normalizedReducedName OR
                                              cdc.normalizedStreetAndNumber != cdd.normalizedStreetAndNumber)
                  ) > 0
            THEN 1
        ELSE 0 END "creditor"

FROM ' + QUOTENAME(@clientstaging) + N'.backend_Case c
        JOIN ' + QUOTENAME(@clientstaging) + N'.backend_CaseProcess p ON p.caseID = c.id
        JOIN ' + QUOTENAME(@clientstaging) + N'.backend_CaseData d on c.id = d.caseID
        JOIN ' + QUOTENAME(@clientstaging) + N'.md_CaseDocumentType t ON t.id = d.caseDocumentTypeID
WHERE c.uploadedByFastLane = 0
  AND p.processStepID != 0
  and c.deleted = 0
  and c.archived = 1';

EXECUTE sys.sp_executesql @sql;

Also, as a side note, I doubt you need all those subqueries; they will be awful for performance.

回复 原文
~没有更多了~

关于作者

幻梦

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

櫻之舞

文章 0 评论 0

弥枳

文章 0 评论 0

m2429

文章 0 评论 0

寻找一个思念的角度

文章 0 评论 0

野却迷人

文章 0 评论 0

我怀念的。

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文