top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Joining null in SQL Server, Oracle and informatica?

+2 votes

I have two tables to join with a column (say emp_id).. if emp_id in both the tables have null values, how will SQL Server and Oracle treat???

I read that informatica will neglect the NULL rows when joining..if I handle the null, by substituting -1, a cross-join will happen which i don't want..

What can I do here? (I cannot completely neglect the rows which has NULL)

posted Mar 21, 2014 by Madhavi Kumari

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

You can't join on colA = colB and expect NULLs to compare as equal. Depending on your needs (assuming perhaps some sort of table synchronisation need below) three approaches can be done :

  1. Use COALESCE to substitute a value such as -1 in place of null if a suitable value exists that can never occur in your actual data. COALESCE(Table1.colA,-1) = COALESCE(Table2.colB,-1)

  2. Use both an IS NULL and equality check on all joining columns.

  3. Use INTERSECT (nulls will be treated as equal). Possibly in a derived table that you can JOIN back onto.

answer Mar 21, 2014 by Shweta Singh
Similar Questions
+1 vote

I want to have compatible SQL for both Oracle database and Microsoft SQL server.

I want a compatible SQL expression that will return true for not null and not empty strings.

If I use:

column <> ''

it will work on Microsoft SQL server but not on Oracle database (as '' is null for Oracle)

If I use:

len(column) > 0

it will work on Microsoft SQL server but not on Oracle database (since it uses length() )

0 votes

I have Informatica 9 and an Oracle database in my system. Now I want to install a SQL Server database in my system and add this database to Informatica.

Is that possible ?

Purpose: I need to migrate some of the tables from SQL Server to Oracle database using informatica.

Could anyone let me know, after installing SQL Server, how can I add this SQL Server database to informatica for creating mapping?

+1 vote

I have a Time column in my SQL Server table which has data like (14:00:00.0000000).

I am trying to load this type of data in Oracle table which has corresponding column datatype as timestamp. I am trying to convert the time column in SQL server to timestamp column in oracle using Informatica.

Whats the best way to do it?

0 votes

I installed informatica PC8.6 on Windows XP and SQL Server 2008 R2 on another machine. How can I access this database from informatica pc.8.6. and is it possible to access?

+1 vote

I am using below statement but i got error.
ADD xxx_name varchar2(50) not null;

error message:
ORA-01758: table must be empty to add mandatory (NOT NULL) column