This content has been marked as final. Show 9 replies
You are getting the error because of limitations on sql generated. With Oracle LONG columns cannot appear in certain parts of SQL statements:
* WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the
DISTINCT operator in SELECT statements
* SQL functions (such as SUBSTR or INSTR)
* expressions or conditions
* select lists of queries containing GROUP BY clauses
* select lists of subqueries or queries combined by set operators
* select lists of CREATE TABLE AS SELECT statements
* Within a single SQL statement, all LONG columns, sequences,
updated tables, and locked tables must be located on the same
Suggestion 1: Make sure your generated query is not doing the above things. First thing Distinct will come in the obiee queries by default. Try to avoid distinct,group by,order by in your reports.
Suggestion 2: Try to do direct database request for the report which needed LONG column, in the database request avoid all above conditions.
Hope it helps.,
Thank you for your reply,
I would be more clear about my question now,
--> I am not using any measure in report, it's completely with dimensions. So we can't expect this column to be present in GROUP BY clause
--> For the first time, by watching SQL, i got to know that it's using in ORDER BY clause, to avoid this i mentioned ORDER BY for first column, so that BI Server applies ORDER BY on only that particular column, instead of applying ORDER BY on all dimensions in the criteria.
--> Finally i am not applying any functions on this column and also not using in conditions means in WHERE clause
If i can apply your suggestion 2 on my report, i can't put column filter prompts or dashboard prompts which need to be applied on my report...
Please any other suggestions?
Thanks & Regards
Edited by: Kishore Guggilla on Nov 5, 2008 7:07 PM
Edited by: Kishore Guggilla on Nov 24, 2008 2:41 PM
I run the query in TOAD, and i was identified the error because of what this error is coming... because of DISTINCT...
I have taken care of avoiding that column in ORDER BY by putting the order by on another column..
Now my question is how to avoid the DISTINCT clause in SQL generated by OBIEE...
If the first column in criteria is measure then it avoids the DISTINCT... but if i use measure... group by will come.. and group by should not be used here...
If this is done... my problem will be resolved...
Ofcourse... i am getting duplicate rows...
Still i want the answer how to avoid DISTINCT?
Expecting answer from you...
Thanks & Regards
Create a function which will change the long to varchar and call the same in RPD physical layer...Example
create or replace
FUNCTION longtovarchar2_4000 (p_tname IN varchar2, p_cname IN varchar2, p_rowid IN ROWID)
l_cursor integer DEFAULT DBMS_SQL.open_cursor ;
l_long_val varchar2 (4000);
l_buflen number := 4000;
l_curpos number := 0;
DBMS_SQL.parse (l_cursor, 'select ' || p_cname || ' from ' || p_tname || ' where row_id = :x1', DBMS_SQL.native);
DBMS_SQL.bind_variable (l_cursor, ':x1', p_rowid);
DBMS_SQL.define_column_long (l_cursor, 1);
l_n := DBMS_SQL.execute (l_cursor);
IF (DBMS_SQL.fetch_rows (l_cursor) > 0)
call: SELECT longtovarchar2_4000 ('Table A','NOTE',Table A.ROW_ID) NOTE,Table A.row_id
FROM Table A
Edited by: user11954081 on Sep 29, 2009 6:44 AM
There may be many solution for this, i am providing only one among them.
Assuming that Fact and Dimension are directly Connected.In this example I am converting LongVarchar to Varchar
There are few steps which should be followed:
1)Create a Opaque view with an Alias for the required column and Cast that Column to required data type along with the key column.
ex: Select CAST(DESCRIPTION as VarChar(4000)) as DESCRIPTION,ID from DIM_TABLE;
Here DESCRIPTION is the required Column.
2)Make exact join for the Opaque view with the Fact .
3)In BMM, we need to divide fact in to two logical tables.Fact columns in to one L.T and Nonfacts in to one L.T
4) Fact L.T should contain only measure columns & non Fact L.T should contain non-measure coulmns.
5)We should make a join between Fact and Non-fact L.T in BMM.
6)Then create Dimnesion Heirarchy for the Non-Fact,and make sure all the coulmns in the Non-fact are at detail level.
7)Drag the Dimnesion column from the Opaque view in to Non- Fact L.T ,which we need and set the logical level to detail.
8)And in the Content tab of Fact L.T.S set the level for the Non-Fact to Detail.
9)Pull the column required coulmn from the Non-fact in to the required Presentation table of the Presentation layer.
by this we can have the our required column in order by, group by or in any criteria.
Edited by: 1002781 on Apr 26, 2013 10:13 AM