Limitations of Update statement with Co-related Sub Query

When the co-related subquery is used to update a table, the subquery table should have combination for all the key values available in the main table. If the combination is not available in the inner table, then, for the combination, the main table will be updated as NULL for the column we do the update.

Employee
EmpID	EmpName
1	A
2	B
3	C
Sub
EmpID	EmpName
2	D
3	E

Assume, there are two tables one is Employee table which is the main table and the table should be updated with the new value from the table Sub. Below query is written to update the values

UPDATE EMPLOYEE E SET EMPNAME = (SELECT EMPNAME
                                                                             FROM SUB S
                                                                              WHERE E.EMPID = S.EMPID)


The result will be updating the employee IDs 2 and 3 and the employee ID 1 will be updated is null, which is unexpected.

Employee
EmpID	EmpName
1
2	D
3	E

The workaround would be to first insert the keys and values from Employee table to Sub table that are not available in the Sub table and to proceed with the actual update


 

FTP Connection in Informatica

Files provided in the shared path can be read directly through 'FTP' connection option available in Informatica. In Workflow Manager, we can see this option under the Connections menu.

Mappings can be developed as such developed for flat file sources. Before configuring the session, the FTP connection should be defined as shown below

A Name for the connection, Credentials and the Hostnames are required.

Once the connection is defined, the connection name can be referred in the session, as shown below.

Once the connection type is selected as FTP and the connection name is selected under value, the file name should be given in the connection properties. The boxed icon shown above should be clicked to provide the same.

Remote filename should be provided as shown above.

There is an option 'Is Staged' which is useful to FTP the file to the local path and the file reading can be started. This option is also helpful when the file should be archived after the ETL process for future reference.
 

Tax calculation excel sheet

http://www.ynithya.com/taxcalc/