Is there an error in the request?

Question

Oracle

Hello)

When you run a query

with rec(pr.pid,cid,par_cid,name,incoming,outcoming,balance) as (
  select
    pr.pid,
    null as cid,
    pr.rcid as par_cid,
    pr.pname as name,
    sum(case when incoming = '1' then rec.quantity end) incoming,
    sum(case when incoming = '0' then rec.quantity end) outcoming,
    sum(case when incoming = '1' then 1 else -1 end * rec.quantity) balance
  from
    products pr
    inner join records rec on rec.rpid=pr.pid
  group by 
    pr.pid,
    pr.rcid,
    pr.pname
  union all select
    null,
    cat.cid,
    cat.par_cid,
    cat.rname,
    incoming,
    outcoming,
    balance
  from
    rec
    inner join catalog cat on cat.cid = rec.par_cid)
select
  pid,
  cid,
  name,
  sum(incoming),
  sum(outcoming),
  sum(balance)
from
  rec
group by
  pid,
  cid,
  name;

Oracle writes an error: "ORA-02000: missing ) keyword". Tell me what's wrong?

Answer 1

Add brackets:

sum((case when incoming = '1' then 1 else -1 end) * rec.quantity) balance

Answer 2

Corrected, but also swears "ORA-02000: missing ) keyword"

Answer 3

The first thing that catches your eye is that the select contains fields that do not participate in either aggregation or grouping.

select
    pr.pid,
    null as cid,
    pr.rcid as par_cid,
    pr.pname as name,
    sum(case when incoming = '1' then rec.quantity end) incoming,
    sum(case when incoming = '0' then rec.quantity end) outcoming,
    sum(case when incoming = '1' then 1 else -1 end * rec.quantity) balance
  from
    products pr
    inner join records rec on rec.rpid=pr.pid
  group by 
    pr.pid,
    pr.rcid,
    pr.pname

So it’s worth starting with the first select in with,
then it with union
, and then further