Cannot use semicolon in SQL statement. What do I do instead?

Question

A sql interface won't let me put semicolons in my SQL queries. What do I do to circumvent this?

select country.country, count(customer.customer_id) as country_count
from customer
join address on customer.address_id = address.address_id
join city on address.city_id = city.city_id
join country on city.country_id = country.country_id
group by country.country_id
order by country_count desc;
limit 10

If I kill the semicolon the interface gives:

Error: ORA-00907: missing right parenthesis.

If I put in the semicolon the interface gives:

Error: ORA-00911: invalid character

In my sqllite program the query works just fine without the ;.

Does Oracle really have `limit`? (I know newer Oracle versions have `FETCH FIRST`, and I think older versions have ROWNUM?) — jarlh, Jul 05 '16 at 06:55
Look at http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering — Jonny, Jul 05 '16 at 06:56
Where in the [Oracle manual](https://docs.oracle.com/database/121/SQLRF/toc.htm) did you find `limit`? — a_horse_with_no_name, Jul 05 '16 at 07:01

score 2 · Answer 1 · answered Jul 05 '16 at 06:55

2

Put the semicolon after the LIMIT , the semicolon always comes at the end of the query.

order by country_count desc
limit 10;

Note that this is MySQL syntax, so it's not weird this query throws an error. You should use rownum :

SELECT * FROM (
    select country.country, count(customer.customer_id) as country_count
    from customer
    join address on customer.address_id = address.address_id
    join city on address.city_id = city.city_id
    join country on city.country_id = country.country_id
    group by country.country_id
    order by country_count desc) t
WHERE rownum < 11;

answered Jul 05 '16 at 06:55

sagi

36,554
5
46
75

Alias `t` should not be present I think – Pரதீப் Jul 05 '16 at 06:59
That doesn't really matter , doesn't it ? @Prdp – sagi Jul 05 '16 at 07:00
In oracle I heard some where that for derived tables we should not use alias may be am wrong ;) – Pரதீப் Jul 05 '16 at 07:05
@Prdp - wherever you heard it that was duff advice. The internet is full of it. – APC Jul 05 '16 at 07:29
@prdp Perhaps the advice was "you cannot create a table or derived table alias using the `AS` keyword". However, an alias without the `AS` works fine. It's a common mistake because most other databases allow `AS` for both column aliases and table aliases. And it's a bit inconsistent, I'm not sure why Oracle has different alias rules for columns and tables. – Jon Heller Jul 05 '16 at 17:22

score 2 · Answer 2 · answered Jul 05 '16 at 06:56

You are looking for this

SELECT *
FROM   (SELECT country.country,
               Count(customer.customer_id) AS country_count
        FROM   customer
               JOIN address
                 ON customer.address_id = address.address_id
               JOIN city
                 ON address.city_id = city.city_id
               JOIN country
                 ON city.country_id = country.country_id
        GROUP  BY country.country_id
        ORDER  BY country_count DESC)
WHERE  ROWNUM <= 10

In Oracle LIMIT keyword is not supported use ROWNUM instead to limit the records

score 1 · Answer 3 · answered Jul 05 '16 at 07:29

If you have Oracle 12, you can use FETCH FIRST:

     SELECT country.country, COUNT (customer.customer_id) AS country_count
       FROM customer
            JOIN address ON customer.address_id = address.address_id
            JOIN city ON address.city_id = city.city_id
            JOIN country ON city.country_id = country.country_id
   GROUP BY country.country_id
   ORDER BY country_count DESC
FETCH FIRST 10 ROWS ONLY

If not, actually FETCH FIRST is syntactic sugar for this:

  SELECT country, country_count
    FROM (  SELECT country.country, 
                   COUNT (customer.customer_id) AS country_count, 
                   ROW_NUMBER () OVER (ORDER BY COUNT (customer.customer_id) DESC) rn
              FROM customer
                   JOIN address ON customer.address_id = address.address_id
                   JOIN city ON address.city_id = city.city_id
                   JOIN country ON city.country_id = country.country_id
          GROUP BY country.country_id)
   WHERE rn <= 10
ORDER BY country_count DESC

and you can use it in Oracle versions < 12

Cannot use semicolon in SQL statement. What do I do instead?

3 Answers3