CODEWITHSUNDEEP
sqloracle-databaselistagg
Baibs
Baibs

Reputation: 33

Oracle - ORA-00907: missing right parenthesis

I just can't find what is wrong here. Trying to select path to the article pictures. ORA-00907: missing right parenthesis

select artnr, listagg(path, bildnr '.' filtyp) within group (order by bildnr) as sökväg
from kund 
where knr in (select knr
            from kundorder
            where ordnr in (select ordnr
            from orderrad
            where artnr in (select artnr
            from artikel
            where bildnr in (select bildnr
            from artikelbild))))
            order by artnr;

An answer should be:

Upvotes: 0

Views: 458

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Obvious error is in listagg (missing concatenation):

No : listagg (path, bildnr '.' filtyp)
Yes: listagg (PATH, bildnr || '.' || filtyp)

The rest should be OK (as far as syntax is concerned; can't tell whether query will (or will not) return what you want).

Also, perhaps you'd rather switch to JOINs instead of using bunch of nested subqueries, e.g.

  SELECT u.artnr,
         LISTAGG (u.PATH, u.bildnr || '.' || u.filtyp)
            WITHIN GROUP (ORDER BY u.bildnr) AS sokvag
    FROM kund u
         JOIN kundorder o ON u.knr = o.knr
         JOIN orderrad r ON r.ordnr = o.ordnr
         JOIN artikel a ON a.artnr = r.artnr
         JOIN artikelbild b ON b.bildnr = a.bildnr
ORDER BY u.artnr;

As of ORA-30496: Argument should be a constant: apparently, that's true.

Code you'd like to run:

SQL> select listagg(e.ename, e.job ||'.'|| to_char(e.deptno))
  2           within group (order by null) result
  3  from emp e
  4  where rownum <= 3;
select listagg(e.ename, e.job ||'.'|| to_char(e.deptno))
                        *
ERROR at line 1:
ORA-30496: Argument should be a constant.

Let's try to fool Oracle and "prepare" the separator:

SQL> with temp as
  2    (select e.ename, e.job ||'.'||to_char(e.deptno) separator
  3     from emp e
  4     where rownum <= 3
  5    )
  6  select listagg(t.ename, t.separator)
  7           within group (order by null)
  8  from temp t;
select listagg(t.ename, t.separator)
                        *
ERROR at line 6:
ORA-30496: Argument should be a constant.

Still no luck. But if a separator really is a constant (comma in my example), then it works:

SQL> select listagg(e.ename ||': ' || e.job, ', ')
  2           within group (order by null) result
  3  from emp e
  4  where rownum <= 3;

RESULT
------------------------------------------------------------------------
ALLEN: SALESMAN, SMITH: CLERK, WARD: SALESMAN

SQL>

Upvotes: 3

Related Questions