Nested parentheses in WHERE clause

Question

I've been reworking the parentheses on this query for a bit to no avail. Can someone point me to the particular spot that is likely causing the error? Here's the error message:

Here's the query:

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())
);

Seems fine to me on first read; you have way too many parentheses that are unnecessary, ie. `((tExceptionsAll1.STL)>0)` can just be `tExceptionsAll1.STL)>0`, and you should alias tables in join so it reads easier, ie. `from FROM PCARDS_ILL_DBO_CARD as X`, then you can just use `X` to refer to that table in your `select` and `where` clauses — user8834780, Oct 18 '18 at 20:14
The error message shows a single right parentheses after getDate() but your code has two, is this really the code that generated that error? — Joakim Danielson, Oct 18 '18 at 20:28

Schwern · Answer 1 · 2022-02-01T22:00:09.663

tl;dr: Access has no getdate function.

But let's make that query easier to read anyway.

Spacing the query out can make it much easier to follow.

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())
);

A good text editor, such as Atom will match parens for you. They all appear to balance fine.

While parens are good to make some precedence issues explicit, like that or, too many just gum things up. There's no need to put parens around every comparison. Let's strip some out. Let's also alias the tables to eliminate redundancy.

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()
);

You can probably come up with better table aliases than I can.

Your error is Wrong number of arguments used with function in query expression. Now that everything is spaced out, we can see there are just two function calls: getdate() and CLNG(). CLNG is an Access fuction but getdate() is not! Instead we should be using date().

FYI you started with "Access has no getdate() function" but then kept it in the re-formatted, aliased query — user8834780, Oct 18 '18 at 21:53
@user8834780 I don't actually know Access. Once I was done reformatting the query it was obvious there were only two function calls. So I Googled them. :-) Now that the query is well-formatted and the problem is known, fixing it is left as an exercise for somebody who knows Access. — Schwern, Oct 18 '18 at 22:06
@user8834780 I've added that as a conclusion. Thanks for noticing! — Schwern, Oct 18 '18 at 22:27
Thanks for the help- here's an update: The code provided works (with the 'date()' for 'getdate()' substitution) if I only run one of the WHERE conditions. The code runs successfully when either condition is used, but when I combine them with the OR I get a 'Data type mismatch in criteria expression.' error. I'm not sure how to interpret this. Can someone advise? — Katie, Oct 25 '18 at 20:35
@katie It would be best if you asked a new question about that. — Schwern, Oct 25 '18 at 21:21

Nested parentheses in WHERE clause

1 Answers1