Link to home
Start Free TrialLog in
Avatar of amoran
amoran

asked on

adding DISTINCT?

hi

i do this

SELECT rnum, english, sd_table_primary_key FROM (SELECT ROWNUM rnum, s.* FROM (SELECT SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLICABLE.SD_Table_Primary_Key, LANGUAGE_SD_COMPANY_APPLICABLE.Company_No, English FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLICABLE WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID) AND (SYSTEM_DATA.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID = 63) ORDER BY SYSTEM_DATA.Implementation_ID, SYSTEM_DATA.SD_ID, UPPER(LANGUAGE_SD_COMPANY_APPLICABLE.English) ASC) s WHERE upper(English) LIKE '%JOHN%') WHERE (rnum >= 1) AND (rnum < 10)

and it works fine but it is returning doubles of some rows?

Can i do something like

SELECT distinct rnum, english, sd_table_primary_key FROM (SELECT distinct ROWNUM rnum, s.* FROM (SELECT SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLICABLE.SD_Table_Primary_Key, LANGUAGE_SD_COMPANY_APPLICABLE.Company_No, English FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLICABLE WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID) AND (SYSTEM_DATA.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID = 63) ORDER BY SYSTEM_DATA.Implementation_ID, SYSTEM_DATA.SD_ID, UPPER(LANGUAGE_SD_COMPANY_APPLICABLE.English) ASC) s WHERE upper(English) LIKE '%JOHN%') WHERE (rnum >= 1) AND (rnum < 10)

(i tried this and it doesnt limit those duplicate rows)

thanks
AM
Avatar of Kevin Hill
Kevin Hill
Flag of United States of America image

If it doesn't limit it, then at least one of the fields in your select contains information that is not a duplicate.  Is your PK field in there?
Without knowledge of your tables and keys I would guess that it is the ROWNUM that is causing you problems. Try:

SELECT rnum, english, sd_table_primary_key
  FROM ( SELECT ROWNUM rnum, s.*
*****                             vvvvvvvvv
                FROM (SELECT DISTINCT SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLICABLE.SD_Table_Primary_Key, LANGUAGE_SD_COMPANY_APPLICABLE.Company_No, English
******                           vvvvvvvvv
                             FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLICABLE
                           WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID)
                               AND (SYSTEM_DATA.Implementation_ID = 16)
                               AND (LANGUAGE_SD_COMPANY_APPLICABLE.Implementation_ID = 16)
                               AND (LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID = 63)
                          ORDER BY SYSTEM_DATA.Implementation_ID, SYSTEM_DATA.SD_ID, UPPER(LANGUAGE_SD_COMPANY_APPLICABLE.English) ASC) s
                WHERE upper(English) LIKE '%JOHN%')
      WHERE (rnum >= 1) AND (rnum < 10)

Also, why the plethora of parentheses on the individual predicates? The serve no function.

Regards

Chedgey
Avatar of amoran
amoran

ASKER

chedgy

that gives me

Error Type:
Database.ExecuteSqlReturningRecordset (0x80040E14)
ORA-01791: not a SELECTed expression [Rethrown]

thanks
Did you take out the two lines with ***** on them?

Take your original query and add the DISTINCT after the thrid (inner-most) SELECT statement on the first line and see what happens.

Regards

Chedgey
Avatar of amoran

ASKER

Yes I did that.

I didnt include the  vvvvvvvvv

I added the DISTINCT after the thrid (inner-most) SELECT statement on the first line and it gave me that error.

Thanks
AM
OK, spotted it. The meaning of the ORA-1791 is:

  ORA-01791 not a SELECTed expression
  Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.
  Action: Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.

So I suggest that on your inner-most SELECT you make the selected items and the ORDER BY list the same. Only order by items and expressions that you have in the SELECT list.

I hope that works for you as I am off home now after a long day in the office.

Regards

Chedgey
Avatar of amoran

ASKER

i removed some of the data in the order by column and it wokrs better now

SELECT rnum, english, sd_table_primary_key FROM (SELECT ROWNUM rnum, s.* FROM (SELECT distinct SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLICABLE.SD_Table_Primary_Key, LANGUAGE_SD_COMPANY_APPLICABLE.Company_No, English FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLICABLE,RETURN WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID) AND RETURN.Company_No = 1124 AND (SYSTEM_DATA.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.Implementation_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLICABLE.SD_ID = 63) ORDER BY UPPER(LANGUAGE_SD_COMPANY_APPLICABLE.English) ASC) s WHERE upper(English) LIKE '%%') WHERE (rnum >= 1) AND (rnum < 10)

but now it is still returning duplicate values?
(one unique row is returned twice)
ASKER CERTIFIED SOLUTION
Avatar of EasyWriter
EasyWriter

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial