Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00936 when using distinct
ORA-00936 when using distinct [message #254137] Wed, 25 July 2007 20:47 Go to next message
syang
Messages: 30
Registered: February 2007
Member
Hi All,

I got an ORA-00936 error while using the distinct function in my sql statement. If without the distinct key word, the sql ran fine. What is wrong? Thanks!

distinct(c.CNTCT_PRSN_1ST_NAME) First_Name,
*
ERROR at line 18:
ORA-00936: missing expression

select
distinct(a.SUPLR_ID) SUPLR,
a.BIDDER_NUM,
a.CASH_FLOW_RCPT_IND CSH,
a.BAL_SHEET_RCPT_IND BAL,
a.INCM_STMT_RCPT_IND INC,
a.SCHDL_L_RCPT_IND SCHDL,
a.SCHDL_C_RCPT_IND SCHDC,
a.CRED_RPT_RCPT_IND CRED,
a.FORM_10K_RCPT_IND F10K,
a.EXPNSN_LTR_RCPT_IND EXPS,
a.LGL_NTWRK_MBR_CNTRCT_RCPT_IND NTWK,
a.CRTFCTN_DCMTN_RCPT_IND CERT,
a.FORMA_DCMTN_RCPT_STUS_CD FA,
a.FORMB_DCMTN_RCPT_STUS_CD FB,
b.LGL_BUSNS_NAME,
b.BUSNS_PHNE_NUM,
distinct(c.CNTCT_PRSN_1ST_NAME) First_Name,
distinct(c.CNTCT_PRSN_LAST_NAME) Last_Name
from
supplier_documentation a,
dmepos_supplier b,
supplier_location_contact_pers c
where
a.SUPLR_ID=b.SUPLR_ID and
a.SUPLR_ID=c.SUPLR_ID
order by
a.SUPLR_ID; 

Re: ORA-00936 when using distinct [message #254141 is a reply to message #254137] Wed, 25 July 2007 21:22 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
DISTINCT is not a function that can be applied to a single column. Remove the brackets.

Ross Leishman
Re: ORA-00936 when using distinct [message #254142 is a reply to message #254141] Wed, 25 July 2007 21:46 Go to previous messageGo to next message
syang
Messages: 30
Registered: February 2007
Member
I removed the brackets after distinct, and still got the same error. See below:

distinct c.CNTCT_PRSN_1ST_NAME First_Name,
*
ERROR at line 18:
ORA-00936: missing expression


SQL> l
1 select
2 distinct a.SUPLR_ID SUPLR,
3 a.BIDDER_NUM,
4 a.CASH_FLOW_RCPT_IND CSH,
5 a.BAL_SHEET_RCPT_IND BAL,
6 a.INCM_STMT_RCPT_IND INC,
7 a.SCHDL_L_RCPT_IND SCHDL,
8 a.SCHDL_C_RCPT_IND SCHDC,
9 a.CRED_RPT_RCPT_IND CRED,
10 a.FORM_10K_RCPT_IND F10K,
11 a.EXPNSN_LTR_RCPT_IND EXPS,
12 a.LGL_NTWRK_MBR_CNTRCT_RCPT_IND NTWK,
13 a.CRTFCTN_DCMTN_RCPT_IND CERT,
14 a.FORMA_DCMTN_RCPT_STUS_CD FA,
15 a.FORMB_DCMTN_RCPT_STUS_CD FB,
16 b.LGL_BUSNS_NAME,
17 b.BUSNS_PHNE_NUM,
18 distinct c.CNTCT_PRSN_1ST_NAME First_Name,
19 distinct c.CNTCT_PRSN_LAST_NAME Last_Name
20 from
21 supplier_documentation a,
22 dmepos_supplier b,
23 supplier_location_contact_pers c
24 where
25 a.SUPLR_ID=b.SUPLR_ID and
26 a.SUPLR_ID=c.SUPLR_ID
27 order by
28* a.SUPLR_ID 

Re: ORA-00936 when using distinct [message #254143 is a reply to message #254137] Wed, 25 July 2007 21:50 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
AFAIK only 1 DISTINCT is allowed.
Having more than 1 makes no sense.
Re: ORA-00936 when using distinct [message #254159 is a reply to message #254142] Thu, 26 July 2007 00:53 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DISTINCT (or UNIQUE) applies to the whole row you return not on some fields.

Regards
Michel
Previous Topic: How to set the the user so that he cant access other tablespace?
Next Topic: qUERRY HELP
Goto Forum:
  


Current Time: Wed Dec 07 04:54:06 CST 2016

Total time taken to generate the page: 0.18142 seconds