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.

Parsing a file in ODI

656730Aug 26 2008 — edited Aug 26 2008

Hello all,

I am trying to read a value from a file and store it in a variable in ODI. The file is a flat file with a single value in the following format

*2007-12-29*

What I would like to do is store that value as '20071229' in a variable. What I have done is create a file datastore in ODI that points to my file.

I have tried creating a variable and entering this in the 'refresh' tab

SELECT NEXT_DT FROM DONE_FILE (where NEXT_DT is what I called the column in my datastore, and DONE_FILE is the name of the datastore). The logical schema is also selected correctly and it points to the right physical scheme. The error I am getting is

"Error while executing query
Invalid format description"

So how can I remove the hyphens from this value and store it in a variable?

Your help is much appreciated.

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.

Locked on Sep 23 2008

Added on Aug 26 2008

#business-intelligence-applications, #data-integrator, #file, #system-management-and-integration, #variable

1 comment

487 views

Analytics Software

Parsing a file in ODI

Comments

Post Details