Skip to Main Content
Forums

Oracle Database Discussions

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.

Interoperability Support Between Different Oracle Versions

682275Jan 29 2009 — edited Jan 29 2009
Hello,

I wants to read an oracle metalink doc, but I still waiting for my acc. and it's very urgent.
Can somebody post it for me?

Client / Server / Interoperability Support Between Different Oracle Versions, note 207303.1


Thanks,
Zilian

Comments

436063

Hi,

Hopefully this will solve your problem

vineet@ORCL>desc temp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(30)

vineet@ORCL>select * from temp;

A
------------------------------
5500
554
5540
5539

Elapsed: 00:00:00.04
vineet@ORCL>l
  1  select * from
  2  (
  3  select * from temp
  4  where a in (
  5             select substr(dual_q.input, 1 , in_list.rn )
  6             from (
  7                       select input , length(input) ln
  8                             from (select '&input' input from dual)
  9                  ) dual_q,
 10                     (
 11                             select row_number() over (order by 1) rn from dual
 12                             group by cube(1, 2, 3,4, 5)
 13                     ) in_list
 14             where  dual_q.ln >= in_list.rn
 15     )
 16  order by 1 desc
 17  )
 18* where rownum < 2
vineet@ORCL>/
Enter value for input: 5544
old   8:                                from (select '&input' input from dual)
new   8:                                from (select '5544' input from dual)

A
------------------------------
554

Elapsed: 00:00:00.04
vineet@ORCL>/
Enter value for input: 55397
old   8:                                from (select '&input' input from dual)
new   8:                                from (select '55397' input from dual)

A
------------------------------
5539

Elapsed: 00:00:00.03
vineet@ORCL>/
Enter value for input: 55409
old   8:                                from (select '&input' input from dual)
new   8:                                from (select '55409' input from dual)

A
------------------------------
5540

Elapsed: 00:00:00.03
vineet@ORCL>

Vineet

146850
here is the magic for you.
SELECT MAX (column_a)
	   KEEP (DENSE_RANK FIRST ORDER BY LENGTH(column_a) DESC) magic_4u
  FROM t
 WHERE column_a IN (SELECT     SUBSTR (str, 1, LEVEL)
                          FROM (SELECT :input str
                                  FROM DUAL)
                    CONNECT BY LEVEL <= LENGTH (str))
for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning
Laurent Schneider
keep it simple !
SQL> create table t as select '5500' a from dual
 union select '554' from dual 
 union select '5540' from dual 
 union select '5539' from dual;

Table created.

SQL> select max(a) a from t where '&input' like a||'%';
Enter value for input: 5544
A
----
554
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 26 2009
Added on Jan 29 2009
#general-database-discussions
2 comments
229 views