top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to Update a single column through informatica?

+1 vote
623 views

I have a target table with the following attributes:

PARTY_ID PK
START_DATE PK
STATUS_CD PK
END_DATE

I have a dynamic lookup which is returning me 1(insert) 2(update) 0 (duplicate) for each row from source table.

What i want is when i get 2(update) to add an END_DATE to the updated row without changing anything else.

For example i have the following row in my target table:
1 12/01/2014 2 NULL

and i get this row from my source table:
1 14/01/2014 6 NULL

What i want is to add ONLY the end date to the target table without anything else. LIKE:
1 12/01/2014 2 14/01/2014

I know how to update the whole row but i dont know how to update only one column.

Schema:
CREATE SET TABLE IND_MAR_STATUS ,NO FALLBACK ,
 NO BEFORE JOURNAL,
 NO AFTER JOURNAL,
 CHECKSUM = DEFAULT,
 DEFAULT MERGEBLOCKRATIO
 (
  INDIVIDUAL_PARTY_ID DECIMAL(18,0) NOT NULL,
  INDIV_MARITAL_STAT_START_DTTM DATE FORMAT 'YYYY-MM-DD' NOT NULL,
  MARITAL_STATUS_CD VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
  INDIV_MARITAL_STAT_END_DTTM DATE FORMAT 'YYYY-MM-DD',
  ETL_SOURCE_ID DECIMAL(18,0) NOT NULL,
  ETL_EXTRACT_SPEC_ID DECIMAL(18,0),
  ETL_JOB_RUN_ID DECIMAL(18,0))
PRIMARY INDEX ( INDIVIDUAL_PARTY_ID );
posted Sep 18, 2014 by Sachin

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

1 Answer

0 votes

Simply disconnect the target ports you don't want to update (i.e. only PARTY_ID and END_DATE should be connected).

answer Sep 22, 2014 by Shweta Singh
Similar Questions
+2 votes

I want to load multiple columns data into single column in informatica.

0 votes

Suppose if we have duplicate records in a table temp_n. Now I want to pass unique values to t1 and duplicate values to t2 in single mapping? How can we achieve?

0 votes

I have some files that I would like to consolidate into a single database table. The files have similar but different formats. The files look something like this:

FileOne:
•ColA : string
•ColB : string
•ColC : string

FileTwo:
•ColAA : string
•ColBB : string
•ColCC : string

FileThree:
•Col01 : string
•Col02 : string
•Col03 : string

The destination table looks like this:

TableDestination:
•ColFirst : string
•ColSecond : string
•ColThird : string

I want to develop a mapping that ETLs these three files into this one database, but because the column names are different, it looks like I'll have to develop three different mappings, or three different sources, or three different somethings. The problem is that my example is contrived: I actually have many different files that all have different formats and column names, but the data is all very similar.

I would like to develop a single mapping or workflow that can handle all of this by only adding a table that holds the column mappings. Such a table would look like this based on the sample files and sample table above:

TableMappings:
enter image description here

In this way, to edit a column mapping I only have to make an edit this this TableMappings table. I wouldn't have to make any changes at all to the mapping or workflow. Nor would I have to redeploy an application.

What would a mapping or workflow look like that could take advantage of something like this? I assume there'd be a flat file source that takes files from a folder. There would be something in the middle that uses this TableMappings table to map column names. Finally there would be a relational data object that represents my destination database table "TableDestination". I don't know how to put this together though.

+2 votes

Currently, we have a lot of ETLs developed and the underlying table and column names are going to change. For example, the physical names used abbreviated names rather than full names since we wanted to deploy on Oracle, but for now, we decided we will use only SQL Server, hence there is a discussion on using full names at the DB level. This will impact the ETLs that we have already developed.

I was just wondering whether there is any efficient way to remap the ETL to the changed column names? I can provide a file with old and new table/column names as input.

Any help on this will be really appreciated.

+1 vote

How to transform rows into column using Normalizer in Informatica?

...