asked on

Very interesting query which uses LONG datatype column in the query

Hi guys,

Heres an interesting query. This query uses a column which is of LONG datatype. The query works fine. But the query returns duplicate values. So I tried to use DISTINCT or GROUP BY to supress the duplicate values. it gives an error saying ILLEGAL USE OF LONG COLUMN.....

Heres the query:

select alerts.ALERT_NAME, usr.USER_NAME, action.SUBJECT,
alerts.ENABLED_FLAG, action.TO_RECIPIENTS, action.CC_RECIPIENTS, alerts.SQL_STATEMENT_TEXT
from ALR_ACTIONS action, alr_alerts alerts, fnd_user usr
where action.ALERT_ID = alerts.ALERT_ID
and alerts.LAST_UPDATED_BY = usr.USER_ID
and (alerts.LAST_UPDATE_DATE > trunc(sysdate) - 30 or action.LAST_UPDATE_DATE > trunc(sysdate) - 30)

SQL_STATEMENT_TEXT is a LONG datatype.

Help Appreciated. How can i supress duplicates rows from this query.

Thanks

Acton Wang

LONG is depreciated by Oracle and is recommended to use CLOB instead.

LONG can store up to 2G(CLOB is to 4G). there can be only one LONG column and it CAN NOT appear in WHERE,GROUP BY or ORDER BY which is why you get "ILLEGAL USE OF LONG COLUMN".

Acton Wang

also LONG can not be used in DISTINCT, MAX(...) etc. so you can see LONG is really discouranged here.

SOLUTION

Acton Wang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amankhan

ASKER

But this is the standard oracle tables which we should not change.

amankhan

ASKER

Ok kewl,

actonwong, i will try this and will let you know if i face any other problems..

thanks

amankhan

ASKER

Hi actonwong,

I am getting an error saying that row_number() function not found. May be this function is included in oracle 8i and i think they are still using Oracle 7. So, how to implement this in other way.

Help Appreciated.

Thanks

Acton Wang

use :

select * from v$version
/

to identify which oracle they are using.

Oracle 7?

ASKER CERTIFIED SOLUTION

Acton Wang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amankhan

ASKER

Awesome actonwon,

It worked perfect. Now i did come to know how to handle LONG columns while writing queries.

Thanks very much.