CODEWITHSUNDEEP
sqloracle-database
chocoscone
chocoscone

Reputation: 3

missing expression...?

I am recently learning Oracle... and I am not sure why the following code returns me 'ORA-00921: unexpected end of SQL command' all my parentheses are closed and I also have the semicolon at the end. I am not sure what I am missing.

select animal_id, animal_type, name
from animal_outs
where sex_upon_outcome not like 'Intact %'
and animal_id = (select animal_id 
                 from animal_ins
                 where sex_upon_intake like ('Intact %');

Upvotes: 0

Views: 266

Answers (3)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

you don't need to use in with like.

select animal_id, animal_type, name
    from animal_outs
    where sex_upon_outcome not like 'Intact %'
    and animal_id = (select animal_id 
                     from animal_ins
                     where sex_upon_intake like ('Intact %'));

Though better option would be:

select animal_id, animal_type, name
    from animal_outs 
    where sex_upon_outcome not like 'Intact %'
    and exists (select animal_id 
                     from animal_ins
                     where sex_upon_intake like ('Intact %') and animal_outs.animal_id = animal_ins.animal_id);

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

"Unexpected end" is due to missing closing bracket.

Also, consider using IN instead of = if subquery returns more than a single value (I guess it might), as you'd get too_many_rows error.

SELECT animal_id, animal_type, name
  FROM animal_outs
 WHERE     sex_upon_outcome NOT LIKE 'Intact %'
       AND animal_id IN (SELECT animal_id                            --> IN
                           FROM animal_ins
                          WHERE sex_upon_intake LIKE ('Intact %'));  --> closing bracket

Upvotes: 2

Venkataraman R
Venkataraman R

Reputation: 12959

You are missing extra paranthesis in the end. Modified query is:

select animal_id, animal_type, name
from animal_outs
where sex_upon_outcome not like 'Intact %'
and animal_id = (select animal_id 
                 from animal_ins
                 where sex_upon_intake like ('Intact %'));

Upvotes: 1

Related Questions