CODEWITHSUNDEEP
sqloracle-databasesqlplus
NorthernViking
NorthernViking

Reputation: 25

SQLplus AND Operator

Can anyone tell me what is wrong with this query below? from a programming point of view it makes perfect sense but sql does not seem to like it.

SELECT SNAME, YEAR, GPA FROM STUDENT
WHERE (YEAR >= 5 AND GPA <=3.0) AND (WHERE YEAR <=4 AND GPA <= 2.0)
ORDER BY YEAR, GPA DESC ;

I get this error:

Error at line 2 ORA-00936: missing expression

Upvotes: 0

Views: 4277

Answers (4)

Vikram Jain
Vikram Jain

Reputation: 5588

In sql only single time where condition is used by in query :

    SELECT SNAME, YEAR, GPA 
    FROM STUDENT 
    WHERE (YEAR >= 5 AND GPA <=3.0) OR (YEAR <=4 AND GPA <= 2.0) 
    ORDER BY YEAR, GPA DESC ;

For more details , please follow syntax :

     how the rows in the result set will be sorted. This clause is optional.

   Description

    You use the basic SELECT statement shown above to retrieve the
 columns specified in the SELECT clause from the base table specified
 in the FROM clause and store them in a result set.
    The WHERE clause is used to filter the rows in the base table so that
 only those rows that match the search condition are included in the
 result set. If you omit the WHERE clause, all of the rows in the base
 table are included.
    The search condition of a WHERE clause consists of one or more Boolean 
expressions, or predicates, that result in a value of True, False, or 
Unknown. If the combination of all the expressions is True, the row
 being tested is included in the result set. Otherwise, it's not.
    If you include the ORDER BY clause, the rows in the result set are
 sorted in the specified sequence. Otherwise, the sequence of the rows
 is not guaranteed by Oracle. Note
    The syntax shown above does not include all of the clauses 
of the SELECT statement. You'll learn about the other clauses later in this book.

Upvotes: 0

John Woo
John Woo

Reputation: 263723

Remove extra WHERE keyword in your condition.

SELECT SNAME, YEAR, GPA 
FROM STUDENT 
WHERE (YEAR >= 5 AND GPA <=3.0) Or (YEAR <=4 AND GPA <= 2.0)
ORDER BY YEAR, GPA DESC

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

Try changing it to

SELECT  SNAME, 
        YEAR, 
        GPA 
FROM    STUDENT 
WHERE   (YEAR >= 5 AND GPA <=3.0) 
OR  (YEAR <=4 AND GPA <= 2.0) 
ORDER BY YEAR, GPA DESC ;

You specified the KEYWORD WHERE twice.

Upvotes: 0

codaddict
codaddict

Reputation: 455020

The problem is you are having two WHERE clauses in your query.

Also you should be combining the two boolean expressions using an OR operator rather than an AND, so that the complete expression is true when either of the two is satisfied:

SELECT SNAME, YEAR, GPA 
FROM STUDENT 
WHERE (YEAR >= 5 AND GPA <=3.0) OR (YEAR <=4 AND GPA <= 2.0) 
ORDER BY YEAR, GPA DESC ;

Upvotes: 2

Related Questions