Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to compare dates in OBIEE reporting

723000Jul 27 2010 — edited Jul 27 2010
TRUNC(DAY_DT) = TRUNC(SYSDATE)

equalant function in OBIEE reporting.......

Regards,
sp

Comments

sb92075
949512 wrote:
How can i convert from long to varchar2?
I want do that in single sql select.

Edited by: 949512 on 2012-08-14 14:38
http://www.lmgtfy.com/?q=oracle+convert+long+to+varchar2
unknown-7404
>
How can i convert from long to varchar2?
I want do that in single sql select.
>
The simplest method is to use a cursor in PL/SQL. See this Oracle-developer.net article
http://www.oracle-developer.net/display.php?id=430
>
method two: pl/sql
The second method simply takes advantage of PL/SQL's ability to convert LONG data to VARCHAR2 while fetching from cursors.
>
You also don't mention your 4 digit Oracle version or how long the LONG is that you are trying to convert. SQL has a limit of 4000 characters for VARCHAR2 while in PL/SQL the limit is 32K so that could also drive where the conversion is done.
952515
sb92075 wrote:
949512 wrote:
How can i convert from long to varchar2?
I want do that in single sql select.

Edited by: 949512 on 2012-08-14 14:38
http://www.lmgtfy.com/?q=oracle+convert+long+to+varchar2
Very funny. Trust me i used google and didnt find anything interesting.

I want do that i single sql select. I dont want using pl/sql, create table ;/
Hoek
Same question has been asked before, just a few hours ago, amongst others....
2429488
Dom Brooks
I want do that in single sql select.
We can't always have want we want.

LONGs have lots of restictions, they're difficult to work with and for very good reason they are deprecated.

A VARCHAR2 column is limited to 4000 character otherwise you'll need to use a CLOB.

In a CREATE TABLE statement or an INSERT SELECT statement you should be able to use [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions205.htm#SQLRF06134]TO_LOB.

Otherwise you're going to have to just work around it.
SQL> create table t1
  2  (col1 long);

Table created.

SQL> 
SQL> create table t2
  2  as
  3  select col1 from t1;
select col1 from t1
       *
ERROR at line 3:
ORA-00997: illegal use of LONG datatype


SQL> create table t2
  2  as
  3  select to_lob(col1) col1 from t1;

Table created.

SQL> drop table t2;

Table dropped.

SQL> create table t2
  2  (col1 varchar2(4000));

Table created.

SQL> insert into t2
  2  select to_lob(col1) from t1;

0 rows created.

SQL> drop table t2;

Table dropped.

SQL> drop table t1;

Table dropped.

SQL> 
But you can't use TO_LOB in a straight SELECT:
SQL> select to_lob(col1) from t1;
select to_lob(col1) from t1
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got LONG
BluShadow
949512 wrote:
sb92075 wrote:
949512 wrote:
How can i convert from long to varchar2?
I want do that in single sql select.

Edited by: 949512 on 2012-08-14 14:38
http://www.lmgtfy.com/?q=oracle+convert+long+to+varchar2
Very funny. Trust me i used google and didnt find anything interesting.
Well, I just looked on google and found loads of things telling me how to convert longs to varchar2. Your google must be broken... try re-installing it.
I want do that i single sql select. I dont want using pl/sql, create table ;/
Why have you got a long deprecated datatype in the first place?

Answer is, you cannot do it in a single sql select, and you must use either pl/sql or create table as already demonstrated. If you don't want to take those options, then you're not going to do it, so that's your problem, not ours.
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details