Rahmat Rahmat - 6 months ago 75
SQL Question

Getting ORA-00936: missing expression in oracle

distinct(cust_acct.name) as value
^-- error here -- ORA-00936: missing expression


select cust_acct.object_id,distinct(cust_acct.name) as value from nc_objects
cust_acct join nc_objects obj on obj.parent_id=cust_acct.object_id and
cust_acct.object_type_id in(2091641841013994133,2091641841013994134)
join nc_objects payment_account on payment_account.parent_id=obj.object_id and payment_account.object_type_id=9135696689113320983 /* Account Payment */


If distinct is removed able to get values but i need it unique

MT0 MT0
Answer

DISTINCT is not a function it is a keyword used immediately after the SELECT clause and applies to all columns. See the Oracle documentation for SELECT:

Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.

Your query should probably be:

SELECT DISTINCT               -- DISTINCT goes immediately after the SELECT
                              -- and applies to all SELECTed columns
       cust_acct.object_id,
       cust_acct.name as value
from   nc_objects cust_acct
       join nc_objects obj 
       on (    obj.parent_id=cust_acct.object_id
           and cust_acct.object_type_id in(2091641841013994133,2091641841013994134 )
       join nc_objects payment_account
       on (    payment_account.parent_id=obj.object_id
           and payment_account.object_type_id=9135696689113320983 ) /* Account Payment */