GeekInterview.com
Series: Subject: Topic:
Question: 490 of 1133

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
Asked by: cvgopal123 | Member Since Dec-2006 | Asked on: Mar 17th, 2007

View all questions by cvgopal123   View all answers by cvgopal123

Showing Answers 1 - 19 of 19 Answers
jaber

Answered On : Mar 20th, 2007

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

  
Login to rate this answer.
vizaik

Answered On : Mar 21st, 2007

View all answers by vizaik

I think we can merge in SQ transformation

  
Login to rate this answer.
ricktesh

Answered On : 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

  
Login to rate this answer.
vizaik

Answered On : Mar 23rd, 2007

View all answers by vizaik

I know how to join two tables in oracle,sql.  i don't know about informaticawe can join two tables using      exists     commandfor ex:select ename from emp where exists (select * from dept where loc='DALLAS');thank q

  
Login to rate this answer.
rimmi

Answered On : 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.

  
Login to rate this answer.
Moin Ahmed

Answered On : Mar 27th, 2007

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

  
Login to rate this answer.
Dinesh.S

Answered On : Apr 10th, 2007

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

  
Login to rate this answer.
Phani

Answered On : 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.

  
Login to rate this answer.

if both have the same structures we can join them using the union transformationsif i am wrong let me know it

  
Login to rate this answer.

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...RegardsTanya

  
Login to rate this answer.
skippy

Answered On : Nov 16th, 2008

View all answers by skippy

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;

  
Login to rate this answer.
v srilatha

Answered On : Feb 21st, 2010

View all answers by v srilatha

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

  
Login to rate this answer.
bachi123

Answered On : Aug 6th, 2010

View all answers by bachi123

First take the sqtransformation and take one connected lookup, in LKP write the condition then proceed.

  
Login to rate this answer.
prasad

Answered On : Sep 9th, 2011

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

  
Login to rate this answer.
sridkaretl

Answered On : Oct 21st, 2011

View all answers by sridkaretl

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.

  
Login to rate this answer.
SHIV

Answered On : 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...

  
Login to rate this answer.
sridkaretl

Answered On : 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.

  
Login to rate this answer.
jayapraveen

Answered On : Jun 22nd, 2012

View all answers by jayapraveen

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

  
Login to rate this answer.
jayapraveen

Answered On : Jun 22nd, 2012

View all answers by jayapraveen


by using Lookup transformation
LKP Logic

in Condition port Salhisal with this Condition we can join two tables

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.