oracle 缺失关键字,ORACLE SQL：缺少关键字

my following query provokes the error message "Error: ORA-00905: missing keyword" but works with SQLite. I can't find the error.

WITH subA AS (

SELECT customer.first_name, customer.last_name, customer.store_id, film_id from

(SELECT customer_id, film_id from inventory join

(SELECT rental.customer_id, rental.inventory_id from rental join

(select customer_id, substr(first_name, 1, 1), substr(last_name, 1, 1) from customer where substr(first_name, 1, 1) = substr(last_name, 1, 1) AND customer.store_id = 2) as subResults

on subResults.customer_id = rental.customer_id) as subResults2

on inventory.inventory_id = subResults2.inventory_id) as finalsubR

join customer on customer.customer_id = finalsubR.customer_id

),

filmResults AS (

select * from (select title, rating, inventory.film_id, inventory_id from inventory join film on inventory.film_id = film.film_id) where rating = 'PG'

)

select distinct first_name, last_name, store_id from subA join filmResults on subA.film_id = filmResults.film_id ORDER by last_name

解决方案

Try this,.. Oracle does not support AS for table alias.

WITH subA AS

(SELECT customer.first_name

,customer.last_name

,customer.store_id

,film_id

FROM (SELECT customer_id

,film_id

FROM inventory

JOIN (SELECT rental.customer_id

,rental.inventory_id

FROM rental

JOIN (SELECT customer_id

,SUBSTR(first_name, 1, 1)

,SUBSTR(last_name, 1, 1)

FROM customer

WHERE SUBSTR(first_name, 1, 1) = SUBSTR(last_name, 1, 1)

AND customer.store_id = 2) subResults

ON subResults.customer_id = rental.customer_id) subResults2

ON inventory.inventory_id = subResults2.inventory_id) finalsubR

JOIN customer ON customer.customer_id = finalsubR.customer_id)

,filmResults AS

(SELECT *

FROM (SELECT title

,rating

,inventory.film_id

,inventory_id

FROM inventory JOIN film ON inventory.film_id = film.film_id)

WHERE rating = 'PG')

SELECT DISTINCT first_name

,last_name

,store_id

FROM subA JOIN filmResults ON subA.film_id = filmResults.film_id

ORDER BY last_name