3 Replies Latest reply on Jul 26, 2007 5:34 AM by jeneesh

    ORA-00936 when using distinct

    558345
      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;
        • 1. Re: ORA-00936 when using distinct
          6363
          Remove the brackets after distinct.
          • 2. Re: ORA-00936 when using distinct
            558345
            I tried your suggestion to remove the brackets, and still got the same error:

            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
            • 3. Re: ORA-00936 when using distinct
              jeneesh
              You cant use '2' DISTINCT in a SELECT statement.
              DISTINCT means distinct set of rows.
              Whats your requirement..?

              Message was edited by:
              jeneesh
              SQL> select distinct c1,c2
                2  from x;

              C1                           C2
              -------------------- ----------
              1.1                           1
              10                            1
              1                             1
              1.3.1                         1
              1.1.1.1.1.1                   1
              2                             1
              3                             1
              1.3                           1
              1.2                           1
              1.4                           1

              10 rows selected.

              SQL> select distict c1,distinct c2
                2  from x;
              select distict c1,distinct c2
                                *
              ERROR at line 1:
              ORA-00936: missing expression