Skip to main content

Posts

Difference Between Primary key and Unique key

Unique key - It display the unique values which can have one null value where Primary key -   has unique values without null. Whenever you create the primary key constraints, Oracle by default create a unique index with not null.    
Recent posts

SQL Transformation with examples

============================================================================================= SQL Transformation with examples   Use : SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline . We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation. Use SQL transformation in script mode to run DDL (data definition language) statements like creating or dropping the tables. The following SQL statements can be used in the SQL transformation. Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME) DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE) DATA Retrieval Statement (SELECT) DATA Control Language Statements (GRANT, REVOKE) Transaction Control Statements (COMMIT, ROLLBACK) Scenario: Let’s say we want to create a temporary table in mapping while workflow is running for some intermediate calculation. We can use SQL transformat...

Efficient SQL Statements : SQL Tunning Tips

Efficient SQL Statements This is an extremely brief look at some of the factors that may effect the efficiency of your SQL and PL/SQL code. It is not intended as a thorough discussion of the area and should not be used as such. Check Your Stats Why Indexes Aren't Used Caching Tables EXISTS vs. IN Presence Checking Inequalities When Things Look Bad! Driving Tables (RBO Only) Improving Parse Speed Packages Procedures and Functions Check Your Stats The Cost Based Optimizer (CBO) uses statistics to decide which execution plan to use. If these statistics are incorrect the decision made by the CBO may be incorrect. For this reason it is important to make sure that these statistics are refreshed regularly. The following article will help you achieve this aim. Cost Based Optimizer (CBO) and Database Statistics Why Indexes Aren't Used The presence of an index on a column does not guarantee it will be...

Function : NVL2 and COALESCE

NVL2 The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter. The following query shows NVL2 in action. SQL> SELECT * FROM null_test_tab ORDER BY id;           ID COL1        COL2        COL3        COL4 ---------- ---------- ---------- ---------- ----------          1 ONE         TWO         THREE       FOUR          2             TWO         THREE       FOUR        ...

Load the session statistics such as Session Start & End Time, Success Rows, Failed Rows and Rejected Rows etc. into a database table for audit/log purpose.

                                                                                                                                                                     ...

Use Target File Path in Parameter File

                                                                                                                                                                     ...

Insert and reject records using update strategy.

                                                                                                                                                                     ...

Convert Numeric Value to Date Format

                                                                                                                                                                     ...

How to change a string to decimal with 2 decimal places in informatica?

                                                                                                                                                                     ...

Append the data in a flat file for a daily run

                                                                                                                                                                     ...