CODEWITHSUNDEEP
sqloracle-database
Kawumm
Kawumm

Reputation: 21

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

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 ;.

Upvotes: 2

Views: 635

Answers (3)

Mottor
Mottor

Reputation: 1948

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

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

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

Upvotes: 2

sagi
sagi

Reputation: 40481

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;

Upvotes: 2

Related Questions