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.

Oracle 9i DB on Mac OS X

323572Aug 14 2002
Has there been any announcement as to the availability of the Oracle 9i database for Mac OS X? It's been a few months since Oracle and Apple made the announcement and so far no product. I'm dying to try this thing out.

Comments

Aketi Jyuuzou
I like case expression :D
with table1 as  (
select 'xx' col1 from dual union all 
select 'yy' col1  from dual union all
select '01' col1  from dual union all
select '02' col1  from dual union all
select '03' col1  from dual union all
select '04' col1  from dual   ),
table2 as (
select 1 col1 from dual union all
select 2 col1 from dual union all
select 3 col1 from dual union all
select 4 col1 from dual )
select *
  from table1 a,table2 b
 where case when not RegExp_Like(a.col1,'^[0-9]+$')
            then 0
            when to_number(a.col1) = b.col1 then 1
            else 0 end = 1;

CO  COL1
--  ----
01     1
02     2
03     3
04     4
Frank Kulash
Answer
user642297 wrote:
Hello experts,

I have two tables ...
create table1
(
col1 varchar2(20)
);

create table2
(
col1 number(10)
);
with their data as below
Above, you said that table2.col1 was a NUMBER.
Below, it's a VARCHAR2: 
with table1 as
(  select 'xx' col1 from dual union all 
select 'yy' col1  from dual union all
select '01' col1  from dual union all
select '02' col1  from dual union all
select '03' col1  from dual union all
select '04' col1  from dual 
),table2 as 
(
select '01' col1 from dual union all
select '02' col1 from dual union all
select '03' col1 from dual union all
select '04' col1 from dual 
)
Woudn't a better approximation of your real tables be: 
   ),table2 as 
    (
      select 1 col1 from dual union all
      select 2 col1 from dual union all
      select 3 col1 from dual union all
      select 4 col1 from dual 
     )
?
My question
how would i join table1.col1 to table2.col1?? since they are different data types ....i tried to use To_char but then i got 0 zero's,
is ther is any functions i can use ??
Post your code. I think the best way is to use TO_CHAR, but maybe you were using it differently. 
SELECT	*
FROM	table1
JOIN	table2	ON	NVL ( LTRIM (table1.col1, '0')
			    , '0'
			    )	= TO_CHAR (table2.col1)
;
This assumes that table1.col1 never contains decimal points, unary + signs, exponents, or other things like that.
It will handle negative numbers, but if table1.col1 contains leading 0's after the minus sign (e.g. '-0012'), then the solution is a little more complicated.
Marked as Answer by 645300 · Sep 27 2020
645300
Thanks you so much frank!! That worked to me !!
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 19 2002
Added on Aug 14 2002
#general-database-discussions
2 comments
226 views