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

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

`

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, и я не уверен, почему.


sql ora-00928
person Tom    schedule 11.12.2020    source источник
comment
У вас не может быть двойных ключевых слов WITH, см. заголовок stackoverflow.com/questions/19654494/   -  person buddemat    schedule 12.12.2020

Ответы (3)


arrow_upward
2
arrow_downward

Вы не повторяете ключевое слово with для нескольких 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 . . .
person Gordon Linoff    schedule 11.12.2020
comment
Это сработало, спасибо! - person Tom; 12.12.2020

arrow_upward
1
arrow_downward

Я вижу, что это не соответствует синтаксису вложенного 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
person Useme Alehosaini    schedule 12.12.2020

arrow_upward
0
arrow_downward

Вы можете добиться желаемого результата без 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)
person Popeye    schedule 12.12.2020