ORA-00928: missing SELECT keyword while referencing other tables

Question

I am trying to run this code:

`

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

`

But it gives me a missing SELECT keyword error and I am not sure why.

You can't have double `WITH` keywords, see https://stackoverflow.com/questions/19654494/can-we-have-multiple-with-as-in-single-sql-oracle-sql — buddemat, Dec 11 '20 at 22:27

score 2 · Answer 1 · answered Dec 11 '20 at 22:25

You do not repeat the with keyword for multiple CTEs. Instead:

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

score 1 · Answer 2 · answered Dec 12 '20 at 01:47

I see it does not match nested cte syntax.

Try this

;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

score 0 · Answer 3 · answered Dec 12 '20 at 01:27

You can achieve desired result without CTE. You can use having clause as follows:

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)

ORA-00928: missing SELECT keyword while referencing other tables

3 Answers3