Forum Stats

  • 3,784,130 Users
  • 2,254,896 Discussions
  • 7,880,702 Comments

Discussions

What is the order of operation in Where clause?

SQeeL
SQeeL Member Posts: 7 Green Ribbon

Hello,

I am trying to interpret a query in PL/SQL and I'm trying to determine the order of the operation. I have a query that includes the following:

SELECT Table.Field1

FROM Table

WHERE

Table.MyDate1 ">" Table.MyDate2 - 730

First, I'm assuming the 730 subtracts Days. Is 730 subtracted from MyDate2 only or from the entire line?

Tagged:

Best Answer

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond

    Hi,

    Table.MyDate1 ">" Table.MyDate2 - 730

    First, I'm assuming the 730 subtracts Days. Is 730 subtracted from MyDate2 only or from the entire line?

    730 is subtracted from from MyDate2; the result is then compared to MyDate1.

    Whenever you have a comparison operator, such as  >  , the expressions before and after the operator are evaluated first, and the comparison is done last.

    You don't really have double-quotes around the  >  sign, do you?

  • User_H3J7U
    User_H3J7U Member Posts: 836 Gold Trophy

    Precedence of conditions and operators is explained in the SQL Reference.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond
    Accepted Answer

    Hi, @SQeeL

    The SQL Language manual documents the order of in which operators are evaluated. I found the page below:

    About SQL Conditions (oracle.com)

    by looking up "operators, precedence" in the index. Notice that the first thing in Table 6.1 is "SQL operators are evaluated before SQL conditions".

    - is an example of a SQL operator.

    > is an example of a SQL condition.

  • mathguy
    mathguy Member Posts: 10,229 Blue Diamond

    Note that

    Table.MyDate2 - 730
    

    is very likely incorrect. 730 = 365 * 2, and very likely the intent was Table.MyDate2 - 2 years. That's pseudo-code, there is no "2 years" expression in SQL. so you can't actually write it just like that. The programmer approximated that as 730 days, but that won't be quite right if going over a leap year.

    The correct way to do that in the Oracle dialect of SQL is

    add_months(Table.MyDate2, -24)
    
  • SQeeL
    SQeeL Member Posts: 7 Green Ribbon

    Thanks everyone! @Frank Kulash, No I didn't use the quotes in the expression. For some reason, it was the only way to use the symbol in the comment box.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond
    edited Jul 15, 2021 7:33PM

    Hi, @SQeeL

    No I didn't use the quotes in the expression. For some reason, it was the only way to use the symbol in the comment box.

    I've just done some experiments, and it looks like this site interprets a > sign (when you're typing) by itself as a command to quote the line. It does not do anything like that when you paste text containing a > sign, nor when you put it inside double-quotes, or type the inequality sign <> . So if you want to use > signs, either

    • type your message somewhere else (like a text editor) and then paste it here, or
    • type <> , then go back and delete the < sign (or type ">  and then go back and delete the " )..

    If you think that's irritating, that makes two of us.