ORA-907 missing right parenthesis when using AS keyword

Question

I get a ORA-907 with the following sql statement. It looks like that there's a problem with using the AS keyword. When I remove the AS keyword and use complete tablenames in my statement it works as expected.

Is that a known issue with oracle? It's running on an 11g, when more details are needed I've to talk to my admin. I've found some older hints which state that there was a bug, in versions < 11.

SELECT easbwaredgs_t.id, easbwaredgs_t.fk_easbware_id, easbwaredgs_t.mandant, easbwaredgs_t.reg_code_mc,   
       easbwaredgs_t.hazard_code_ident, easbwaredgs_t.add_hazard_code, easbwaredgs_t.haz_code_version,   
       easbwaredgs_t.undg_number, easbwaredgs_t.ship_flashpoint, easbwaredgs_t.flashpoint_type,   
       easbwaredgs_t.cont_dopc, easbwaredgs_t.cont_dop, easbwaredgs_t.cont_phone, easbwaredgs_t.verpack_grp_mc,   
       easbwaredgs_t.ems_nr, easbwaredgs_t.trem_card_nr, easbwaredgs_t.secondimo, easbwaredgs_t.thirdimo,
       (
         SELECT COUNT(*)
         FROM easbwaredgs_t AS k
           LEFT OUTER JOIN easbdgstn_t AS p ON k.id = p.fk_easbwaredgsid
         WHERE k.mandant = '001'
           AND k.fk_easbware_id = 1
           AND p.type_mc = 'TRANSPORT_DGS_LIM_QUANT'
       ) AS cc_is_limited_quantities
FROM easbwaredgs_t
WHERE easbwaredgs_t.mandant = '001'
  AND easbwaredgs_t.fk_easbware_id = 1

You can't use `AS` for table aliases, e.g. `AS k`.[See this answer](http://stackoverflow.com/a/18378411/266304). — Alex Poole, Dec 22 '16 at 11:55

score 3 · Accepted Answer · answered Dec 22 '16 at 11:58

3

Your problem is the as. However, I think your query is intended to be a correlated subquery. This should be written as:

SELECT e.id, e.fk_easbware_id, e.mandant, e.reg_code_mc,   
       e.hazard_code_ident, e.add_hazard_code, e.haz_code_version,   
       e.undg_number, e.ship_flashpoint, e.flashpoint_type,   
       e.cont_dopc, e.cont_dop, e.cont_phone, e.verpack_grp_mc,   
       e.ems_nr, e.trem_card_nr, e.secondimo, e.thirdimo,
       (SELECT COUNT(*)
         FROM easbdgstn_t p ON 
         WHERE e.id = p.fk_easbwaredgsid AND
               p.type_mc = 'TRANSPORT_DGS_LIM_QUANT'
       ) AS cc_is_limited_quantities
FROM easbwaredgs_t e
WHERE e.mandant = '001' AND
      e.fk_easbware_id = 1;

Just to clarify: as is permitted (and desired) for column aliases. It is not allowed for table aliases in Oracle.

answered Dec 22 '16 at 11:58

Gordon Linoff

1,242,037
58
646
786

I was expecting that oracle supports SQL-92. Now I know that oracle just support ANSI-SQL. BTW.: the oracle docs for 11g state that it complains for SQL2003 which than can't be true. – user743414 Dec 22 '16 at 13:10
@user743414 . . . Support for ANSI standard SQL comes with a rider, which explains all the deviations from the standard. This lets vendors say they support the standard even when they don't support all features. – Gordon Linoff Dec 22 '16 at 13:22
Ah ok, I didn't know that. Thank's and have a merry christmas. – user743414 Dec 22 '16 at 14:45

score 2 · Answer 2 · answered Dec 22 '16 at 11:55

2

In oracle using as for a table alias is not valid.
Use only the alias without preceding it with as.

...) t

and not

...) as t

answered Dec 22 '16 at 11:55

David דודו Markovitz

42,900
6
64
88

ORA-907 missing right parenthesis when using AS keyword

2 Answers2

Linked

Related