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_APPLIC ABLE.SD_Ta ble_Primar y_Key, LANGUAGE_SD_COMPANY_APPLIC ABLE.Compa ny_No, English FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLIC ABLE WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLIC ABLE.SD_ID ) AND (SYSTEM_DATA.Implementatio n_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLI CABLE.Impl ementation _ID = 16) AND (LANGUAGE_SD_COMPANY_APPLI CABLE.SD_I D = 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_APPLIC ABLE.SD_Ta ble_Primar y_Key, LANGUAGE_SD_COMPANY_APPLIC ABLE.Compa ny_No, English FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLIC ABLE WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLIC ABLE.SD_ID ) AND (SYSTEM_DATA.Implementatio n_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLI CABLE.Impl ementation _ID = 16) AND (LANGUAGE_SD_COMPANY_APPLI CABLE.SD_I D = 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
i do this
SELECT rnum, english, sd_table_primary_key FROM (SELECT ROWNUM rnum, s.* FROM (SELECT SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLIC
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_APPLIC
(i tried this and it doesnt limit those duplicate rows)
thanks
AM
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_APPLIC ABLE.SD_Ta ble_Primar y_Key, LANGUAGE_SD_COMPANY_APPLIC ABLE.Compa ny_No, English
****** vvvvvvvvv
FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLIC ABLE
WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLIC ABLE.SD_ID )
AND (SYSTEM_DATA.Implementatio n_ID = 16)
AND (LANGUAGE_SD_COMPANY_APPLI CABLE.Impl ementation _ID = 16)
AND (LANGUAGE_SD_COMPANY_APPLI CABLE.SD_I D = 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
SELECT rnum, english, sd_table_primary_key
FROM ( SELECT ROWNUM rnum, s.*
***** vvvvvvvvv
FROM (SELECT DISTINCT SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLIC
****** vvvvvvvvv
FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLIC
WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLIC
AND (SYSTEM_DATA.Implementatio
AND (LANGUAGE_SD_COMPANY_APPLI
AND (LANGUAGE_SD_COMPANY_APPLI
ORDER BY SYSTEM_DATA.Implementation
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
ASKER
chedgy
that gives me
Error Type:
Database.ExecuteSqlReturni ngRecordse t (0x80040E14)
ORA-01791: not a SELECTed expression [Rethrown]
thanks
that gives me
Error Type:
Database.ExecuteSqlReturni
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
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
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
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
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
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_APPLIC ABLE.SD_Ta ble_Primar y_Key, LANGUAGE_SD_COMPANY_APPLIC ABLE.Compa ny_No, English FROM SYSTEM_DATA, LANGUAGE_SD_COMPANY_APPLIC ABLE,RETUR N WHERE (SYSTEM_DATA.SD_ID = LANGUAGE_SD_COMPANY_APPLIC ABLE.SD_ID ) AND RETURN.Company_No = 1124 AND (SYSTEM_DATA.Implementatio n_ID = 16) AND (LANGUAGE_SD_COMPANY_APPLI CABLE.Impl ementation _ID = 16) AND (LANGUAGE_SD_COMPANY_APPLI CABLE.SD_I D = 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)
SELECT rnum, english, sd_table_primary_key FROM (SELECT ROWNUM rnum, s.* FROM (SELECT distinct SYSTEM_DATA.SD_ID, LANGUAGE_SD_COMPANY_APPLIC
but now it is still returning duplicate values?
(one unique row is returned twice)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.