How to join the data of two tables which does not have common columns

How to join the data of two tables which do not have common column

i mean how to perform non equi join in informatica
Eg:just like getting data from emp and salgrade table of oracle where sal is between losal and hisal



thanks
venu

Questions by cvgopal123   answers by cvgopal123

Showing Answers 1 - 21 of 21 Answers

jaber

  • Mar 20th, 2007
 

we can join two different tables which does'nt have common columns by using joiner transformation

  Was this answer useful?  Yes

ricktesh

  • Mar 22nd, 2007
 

in informatica we can't join two tables which does not have common port.

if there is any requirement then we go for source qualifier transformation,where we can use sql override on joining two tables which have no any common coulumn

  Was this answer useful?  Yes

vizaik

  • Mar 23rd, 2007
 

i know how to join two tables in oracle,sql.  i don't know about informatica

we can join two tables using      exists     command

for ex:

select ename from emp where exists (select * from dept where loc='DALLAS');

thank q

  Was this answer useful?  Yes

rimmi

  • Mar 25th, 2007
 

if the two  tables do not have a common column  but their data bases are same then you can use a Source Qualifier but if their data bases are different and u are trying to join the tables then its not possible to join in informatica.

  Was this answer useful?  Yes

Moin Ahmed

  • Mar 27th, 2007
 

Plz if u send me the example query for the above comment title.

  Was this answer useful?  Yes

Dinesh.S

  • Apr 10th, 2007
 

You can achieve the non-equal join result in informatica by using lookup transformation.

  Was this answer useful?  Yes

Phani

  • Jun 22nd, 2007
 


Create dummy column each in two table with same name and data type and assign default value as 1 for both after dragging the columns into SQ and/or Joiner Transformation(s) and use those columns in the join condition.
Hope this will work
All the best.

  Was this answer useful?  Yes

tanya123

  • Feb 26th, 2008
 

Hi,

The best way to join data from two tables which donot have  common columns is first use a sequence generator transformation to generate the keys,then connect it to an expression transformation and then use a joiner transformation to join the tables.I think this will work...

Any better answer will be appreciated...

Regards
Tanya

  Was this answer useful?  Yes

skippy

  • Nov 16th, 2008
 

The relationship between the EMP table and the SALGRADE table is a
non-equijoin, meaning that no column in the EMP table corresponds directly to a
column in the SALGRADE table. The relationship between the two tables is that
the SAL column in the EMP table is between the LOSAL and HISAL column of the
SALGRADE table. The relationship is obtained using an operator other than equal
(=).


SQL> SELECT e.ename, e.sal, s.grade
2 FROM emp e, salgrade s
3 WHERE e.sal
4 BETWEEN s.losal AND s.hisal;

  Was this answer useful?  Yes

v srilatha

  • Feb 21st, 2010
 

If two or more tables from same database (like Oracle db or any other like db2, all table should be from same database) then we merge in SQ transformation i.e
Table1 -> Same SQ for both tables 1 & 2 -> Target 
              |
Table2---|
(Drop one of the table's SQ & Drag all columns to available SQ)
We can join two different tables by using joiner transformation which does not have common columns and are from 2 different databases.

Srilatha

  Was this answer useful?  Yes

prasad

  • Sep 9th, 2011
 

hi phani,
how can u generate that dummy column in two tables . can u explain briefly

  Was this answer useful?  Yes

sridkaretl

  • Oct 21st, 2011
 

hi,
you can use source qualifier transformation if both the tables are from same database and then use sql override. If both the tables are from separate databases then use joiner transformation. I don't think we can use union transformation as both the tables should have same metadata.

  Was this answer useful?  Yes

SHIV

  • Jan 2nd, 2012
 

Hi,


Take one dummy column each in two expression transformations and assign 1 or some value to that column and based on that condition you join.....

Ex(take dummy1 in expr1 and initialize to 1 and take one dummy from expr2 initialize it to 1 and join it based on that dummy columns...)

Thanks,
SHIV...

sridkaretl

  • Jan 6th, 2012
 

Hi,

What you have said is correct, but here what join we are taking is important and the dummy column you are taking cannot or should not be a date function as it consumes time and the join condition is also tough to implement. So, take a column which is less in data with a relation which links with an other column from other table. I guess, you must be knowing the concept of bridge fact which performs the same operation.

  Was this answer useful?  Yes

jayapraveen

  • Jun 22nd, 2012
 

we can do it by taking lookup transformation
example you had taken is Emp table and sal grade table
LKP Logic:
in Condition port sal and sal>hisal
with this Condition we can join two tables and get the data

  Was this answer useful?  Yes

ashish

  • Sep 27th, 2014
 

you can create a dummy port in each source and assign 1 to both port and drag the both sources in joiner tr and give join condition on dummy port, After that you can use filter condition on your location

  Was this answer useful?  Yes

milind

  • Aug 6th, 2015
 

Yes dummy column will work
other way is to use mapping variable :)

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions