时间戳

首页 > 解决方案 > WHERE 子句中的嵌套括号

sql - WHERE 子句中的嵌套括号

问题描述

我一直在修改此查询的括号，但无济于事。有人可以指出可能导致错误的特定位置吗？这是错误消息：

在此处输入图像描述

这是查询：

SELECT 
tExceptionsAll1.ID, 
tExceptionsAll1.CardholderName, 
PCARDS_ILL_DBO_CARD.PERSON_ID, 
tExceptionsAll1.CardType, 
tExceptionsAll1.Duration, 
tExceptionsAll1.ExceptionType, 
tExceptionsAll1.STL AS [Exp STL], 
tExceptionsAll1.CL AS [Exp CL], 
PCARDS_ILL_DBO_CARD.TRANS_LIMIT_AMT AS [Card STL], 
PCARDS_ILL_DBO_CARD.MONTH_LIMIT_AMT AS [Card CL], 
tExceptionsAll1.TerminationDate, 
tExceptionsAll1.DCMNames, 
tExceptionsAll1.ReminderDate
FROM PCARDS_ILL_DBO_CARD 
INNER JOIN tExceptionsAll1 ON (PCARDS_ILL_DBO_CARD.CARD_ID = CLNG(tExceptionsAll1.CardID)) 
      AND (CLNG(PCARDS_ILL_DBO_CARD.PERSON_ID) = tExceptionsAll1.CardholderUIN)
WHERE (
  ((tExceptionsAll1.STL)>0) 
  And ((tExceptionsAll1.CL)>0) 
  And ((PCARDS_ILL_DBO_CARD.TRANS_LIMIT_AMT)<>tExceptionsAll1.STL) 
  And ((PCARDS_ILL_DBO_CARD.MONTH_LIMIT_AMT)<>tExceptionsAll1.CL) 
  And ((tExceptionsAll1.TerminationDate) Is Null)
) 
OR (
  ((tExceptionsAll1.TempSTL)>0) 
  And ((tExceptionsAll1.TempCL)>0) 
  And ((PCARDS_ILL_DBO_CARD.TRANS_LIMIT_AMT)<>tExceptionsAll1.TempSTL) 
  And ((PCARDS_ILL_DBO_CARD.MONTH_LIMIT_AMT)<>tExceptionsAll1.TempCL) 
  And ((tExceptionsAll1.TerminationDate) Is Null) 
  And ((tExceptionsAll1.ReminderDate) < getdate())
);

标签： sqlwhere-clauseparentheses

解决方案

tl; dr：访问没有getdate功能。

但是无论如何，让我们使该查询更易于阅读。

将查询隔开可以使其更容易理解。

SELECT
    tExceptionsAll1.ID,
    tExceptionsAll1.CardholderName,
    PCARDS_ILL_DBO_CARD.PERSON_ID,
    tExceptionsAll1.CardType, 
    tExceptionsAll1.Duration,
    tExceptionsAll1.ExceptionType,
    tExceptionsAll1.STL AS [Exp STL],
    tExceptionsAll1.CL AS [Exp CL], 
    PCARDS_ILL_DBO_CARD.TRANS_LIMIT_AMT AS [Card STL],
    PCARDS_ILL_DBO_CARD.MONTH_LIMIT_AMT AS [Card CL], 
    tExceptionsAll1.TerminationDate,
    tExceptionsAll1.DCMNames,
    tExceptionsAll1.ReminderDate
FROM PCARDS_ILL_DBO_CARD
INNER JOIN tExceptionsAll1 
    ON (PCARDS_ILL_DBO_CARD.CARD_ID = CLNG(tExceptionsAll1.CardID)) AND
       (CLNG(PCARDS_ILL_DBO_CARD.PERSON_ID) = tExceptionsAll1.CardholderUIN)
WHERE (
    ((tExceptionsAll1.STL)>0) And
    ((tExceptionsAll1.CL)>0) And
    ((PCARDS_ILL_DBO_CARD.TRANS_LIMIT_AMT)<>tExceptionsAll1.STL) And
    ((PCARDS_ILL_DBO_CARD.MONTH_LIMIT_AMT)<>tExceptionsAll1.CL) And
    ((tExceptionsAll1.TerminationDate) Is Null)
)
    OR 
(
    ((tExceptionsAll1.TempSTL)>0) And
    ((tExceptionsAll1.TempCL)>0) And
    ((PCARDS_ILL_DBO_CARD.TRANS_LIMIT_AMT)<>tExceptionsAll1.TempSTL) And
    ((PCARDS_ILL_DBO_CARD.MONTH_LIMIT_AMT)<>tExceptionsAll1.TempCL) And
    ((tExceptionsAll1.TerminationDate) Is Null) And
    ((tExceptionsAll1.ReminderDate) < getdate())
);

一个好的文本编辑器，比如Atom会为你匹配括号。他们似乎都平衡得很好。

虽然括号很好地明确了一些优先级问题，比如那样or，但太多只会把事情搞砸。没有必要在每次比较时都加上括号。让我们去掉一些。让我们也给表起别名以消除冗余。

SELECT
    tea1.ID,
    tea1.CardholderName,
    pidc.PERSON_ID,
    tea1.CardType, 
    tea1.Duration,
    tea1.ExceptionType,
    tea1.STL AS [Exp STL],
    tea1.CL AS [Exp CL], 
    pidc.TRANS_LIMIT_AMT AS [Card STL],
    pidc.MONTH_LIMIT_AMT AS [Card CL], 
    tea1.TerminationDate,
    tea1.DCMNames,
    tea1.ReminderDate
FROM PCARDS_ILL_DBO_CARD pidc
INNER JOIN tExceptionsAll1 tea1 
    ON pidc.CARD_ID = CLNG(tea1.CardID) AND
       CLNG(pidc.PERSON_ID) = tea1.CardholderUIN
WHERE (
    tea1.STL > 0 AND
    tea1.CL  > 0 AND
    pidc.TRANS_LIMIT_AMT <> tea1.STL AND
    pidc.MONTH_LIMIT_AMT <> tea1.CL  AND
    tea1.TerminationDate IS NULL
)
    OR 
(
    tea1.TempSTL > 0 AND
    tea1.TempCL  > 0 AND
    pidc.TRANS_LIMIT_AMT <> tea1.TempSTL AND
    pidc.MONTH_LIMIT_AMT <> tea1.TempCL AND
    tea1.TerminationDate IS NULL AND
    tea1.ReminderDate < getdate()
);

您可能会想出比我更好的表别名。

你的错误是Wrong number of arguments used with function in query expression。现在一切都被隔开，我们可以看到只有两个函数调用：getdate()和CLNG(). CLNG 是 Access 函数，但getdate()不是！相反，我们应该使用date()。

推荐阅读