ORA-00928: отсутствует ключевое слово SELECT при ссылке на другие таблицы

Question

Я пытаюсь запустить этот код:

`

      with cart_amount as (select customer_id, count(customer_id) "Count" from in_cart group by customer_id)
with order_amount as (select customer_id, count(customer_id) "Count" from in_order group by customer_id)

select c.first_name "Customer Name",
       c.email "Customer Email"
from in_cart ic join customer c on ic.customer_id=c.customer_id
                join cart_amount ca on ic.customer_id=ca.customer_id
                join order_amount oa on ic.customer_id=oa.customer_id
where (ca."Count" - oa."Count") > 0 
group by c.first_name, c.email

`

Но это дает мне отсутствующую ошибку ключевого слова SELECT, и я не уверен, почему.

Answer 1

Вы не повторяете ключевое слово для нескольких CTE. Вместо:

      with cart_amount as (
      select customer_id, count(customer_id) "Count"
      from in_cart
      group by customer_id
     ),
     order_amount as (
      select customer_id, count(customer_id) "Count"
      from in_order
      group by customer_id
     )
select . . .

Answer 2

Я вижу, что это не соответствует синтаксису вложенного cte.

Попробуй это

      ;WITH cart_amount
AS (
    SELECT customer_id,
        count(customer_id) "Count"
    FROM in_cart
    GROUP BY customer_id
    ),
order_amount
AS (
    SELECT customer_id,
        count(customer_id) "Count"
    FROM in_order
    GROUP BY customer_id
    )
SELECT c.first_name "Customer Name",
    c.email "Customer Email"
FROM in_cart ic
JOIN customer c ON ic.customer_id = c.customer_id
JOIN cart_amount ca ON ic.customer_id = ca.customer_id
JOIN order_amount oa ON ic.customer_id = oa.customer_id
WHERE (ca."Count" - oa."Count") > 0
GROUP BY c.first_name,
    c.email

Answer 3

Вы можете добиться желаемого результата без CTE. Вы можете использовать havingпункт следующим образом:

      select c.first_name "Customer Name",
       c.email "Customer Email"
  from in_cart ic join customer c on ic.customer_id=c.customer_id
  join in_cart ca on ic.customer_id=ca.customer_id
  join in_order oa on ic.customer_id=oa.customer_id
group by c.customer_id, c.first_name, c.email
Having count(distinct ca.pk_col) > count(distinct oa.pk_col)