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!

ODI12c parsing data file name and storing in target

Deena URApr 24 2018 — edited May 3 2018

Is there means to take an input file name (to a mapping - our input files will have a  process_id appended to end), parse out the process_id

and then take this info and use as input to a target column in the mapping?

Are there any blogs or whitepapers regarding this type of thing available?

Thank you!

This post has been answered by Mateus Santilli on Apr 25 2018
Jump to Answer

Comments

isotope
Well, test it:
test@ora>
test@ora> --
test@ora> with date_table as (
  2    select sysdate as dt from dual union all
  3    select sysdate - 1   from dual union all
  4    select sysdate - 2   from dual)
  5  --
  6  select dt, a.a
  7  from date_table d,
  8  (select a from (select level a  from dual connect by 1 = 1) where rownum < 3) a;

DT                 A
--------- ----------
16-JUL-08          1
15-JUL-08          1
14-JUL-08          1
16-JUL-08          2
15-JUL-08          2
14-JUL-08          2

6 rows selected.

test@ora>
test@ora> --
test@ora> -- or
test@ora> --
test@ora> with date_table as (
  2    select sysdate as dt from dual union all
  3    select sysdate - 1   from dual union all
  4    select sysdate - 2   from dual)
  5  --
  6  select dt, a.a
  7  from date_table d,
  8  (select level a from dual connect by level < 3) a;

DT                 A
--------- ----------
16-JUL-08          1
15-JUL-08          1
14-JUL-08          1
16-JUL-08          2
15-JUL-08          2
14-JUL-08          2

6 rows selected.

test@ora>
test@ora>
isotope
Frank Kulash

Hi,

Cross-joining with a counter-table, that is, your

(select a from (select level a  from dual connect by 1 = 1) where rownum < 3) a

for example, or its simpler and more common equivalent

(   SELECT      LEVEL AS a
    FROM        dual
    CONNECT BY  LEVEL <= 3
) a

can give you some of the benefits of iteration. There are differences, which can be important. For example, in a procedural loop, like

for i in 1..:n loop 
    select a_data + from date_table; 
end loop; 

you can be sure that the iteration for i=1 is done before the iteration for i=2, and variables that you set in one iteration can be referenced in the next.
In Oracle 10 (and up) the MODEL feature allows true iteration.

alinux
Thanks for your answers.
this will work for me in the first phase:
select res.level_no + a - 1, res.rp_id
  from (select level_no, rp_id from cont_cust where h = a and rownum < 2) res,
        (select level a from dual connect by level <= 5)
Res table will always return one row.

But if I want to use the level(a) in some condition in res table.

the result of res table to be modified by 1,2,3 or 4(use it in where clause)?

see in bold-that is not possible...but something similar I want to obtain.

thanks
Frank Kulash

Hi,

It looks like you now know how to create a sub-query a that contains the integers 1, 2, ... n. It's as if you had a table with n rows, containing the numbers 1, 2, ..., n. You can use that result set the same way you use any other result set. Usually, that means joining it to some other result set. You can do inner joins, outer joins, or cross joins: each are appropriate for different tasks.
The reason your bolded text:

  from (select level_no, rp_id from cont_cust where h = a and rownum < 2) res,

doesn't work is that the query is based entirely on cont_cust, which (I suppose) doesn't have a column called a. If you want to reference column a from table a, you have to join with table a. "h = a" would be a perfectly valid join condition.

I'm not sure what you're trying to do. Concrete questions are always easier to answer. Can you post a specific example?
For instance:
"I want to display five copies of a given row from a table (say, the row from scott.emp that has the employee named King). I want to number those rows 1 through 5, and flag a given row (say, #3)".

If that were your question, the answer might be:

WITH a AS 
(
    SELECT     LEVEL AS a
    FROM       dual
    CONNECT BY LEVEL <= 5
)
SELECT      a.a
,           emp.ename
,           CASE
                WHEN a.a = 3  THEN 'This is special' 
            END    AS flag
FROM        a
CROSS JOIN  scott.emp
WHERE       ename = 'KING';

Output:

         A ENAME      FLAG
---------- ---------- ---------------
         1 KING
         2 KING
         3 KING       This is special
         4 KING
         5 KING
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 31 2018
Added on Apr 24 2018
18 comments
805 views