Forum Stats

  • 3,872,790 Users
  • 2,266,476 Discussions
  • 7,911,319 Comments

Discussions

Subquery in Select Statement in ODI12C mappings

BCV
BCV Member Posts: 282 Bronze Badge

Hi All,

I want to achieve below SQL query in ODI 12C Mappings , i am mainly looking for the help to implement the subquery in select statement . Please help with reference , your help would be appreciated !

select column1 ,

column2 ,

(select column1 from tab1 a where a.column1 =M.column1 ) column3,

column4 from

TableM M join TableN N

where M.eno = N.eno ;

Tagged:

Best Answer

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,724 Blue Diamond
    edited Jul 28, 2022 12:46PM Answer ✓

    Make it just a normal join, there is nothing in the example requiring it to be a subquery, you can get the same result with a normal join. In your mapping a join has only 2 elements, but nothing forbid you to chain multiple joins.

    It will also help you dealing with errors like if your "tab1" returns more than 1 row for the given condition (by the way, in the example condition you could just keep "M.column1" without any subquery as you are return that exact same value).

    If you want your "column3" to be NULL when there isn't a match, just make it an outer join.

Answers

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,724 Blue Diamond
    edited Jul 28, 2022 12:46PM Answer ✓

    Make it just a normal join, there is nothing in the example requiring it to be a subquery, you can get the same result with a normal join. In your mapping a join has only 2 elements, but nothing forbid you to chain multiple joins.

    It will also help you dealing with errors like if your "tab1" returns more than 1 row for the given condition (by the way, in the example condition you could just keep "M.column1" without any subquery as you are return that exact same value).

    If you want your "column3" to be NULL when there isn't a match, just make it an outer join.

  • BCV
    BCV Member Posts: 282 Bronze Badge

    its working well with outer join , Thanks for the suggestions !