SOURCE QUALIFIER TRANSFORMATION 

1. WHAT IS A SOURCE QUALIFIER? WHAT ARE THE TASKS WE CAN PERFORM USING A SOURCE QUALIFIER AND WHY IT IS AN ACTIVE
TRANSFORMATION? 
2. WHAT HAPPENS TO A MAPPING IF WE ALTER THE DATA TYPES BETWEEN SOURCE AND ITS CORRESPONDING SOURCE QUALIFIER? 
3. SUPPOSE WE HAVE USED THE SELECT DISTINCT AND THE NUMBER OF SORTED PORTS PROPERTY IN THE SOURCE QUALIFIER AND
THEN WE ADD CUSTOM SQL QUERY. EXPLAIN WHAT WILL HAPPEN. 
4. DESCRIBE THE SITUATIONS WHERE WE WILL USE THE SOURCE FILTER, SELECT DISTINCT AND NUMBER OF SORTED PORTS
PROPERTIES OF SOURCE QUALIFIER TRANSFORMATION. 
5. WHAT WILL HAPPEN IF THE SELECT LIST COLUMNS IN THE CUSTOM OVERRIDE SQL QUERY AND THE OUTPUT PORTS ORDER
IN SOURCE QUALIFIER TRANSFORMATION DO NOT MATCH? 
6. WHAT HAPPENS IF IN THE SOURCE FILTER PROPERTY OF SQ TRANSFORMATION WE INCLUDE KEYWORD WHERE SAY, WHERE
CUSTOMERS.CUSTOMER_ID > 1000. 
7. DESCRIBE THE SCENARIOS WHERE WE GO FOR JOINER TRANSFORMATION INSTEAD OF SOURCE QUALIFIER TRANSFORMATION. 
8. WHAT IS THE MAXIMUM NUMBER WE CAN USE IN NUMBER OF SORTED PORTS FOR SYBASE SOURCE SYSTEM? 
9. WHAT IS USE OF SOURCE QUALIFIER IN INFORMATICA? CAN WE CREATE A MAPPING WITHOUT A SOURCE QUALIFIER? 
10. SUPPOSE WE HAVE TWO TABLES OF SAME DATABASE TYPE, RESIDING IN DIFFERENT DATABASE INSTANCE. IF A DATABASE LINK IS
AVAILABLE, HOW CAN WE JOIN THE TWO TABLES USING A SOURCE QUALIFIER IN INFORMATICA PROVIDED THERE ARE VALID JOIN
COLUMNS. 
11. WHAT IS THE MEANING OF “OUTPUT IS DETERMINISTIC” PROPERTY IN SOURCE QUALIFIER TRANSFORMATION? 
12. SCENARIO IMPLEMENTATION 1 

134.What Is The Default Join That Source Qualifier Provides?

Inner equi join.

 136.Differentiate between Source Qualifier and Filter Transformation?

137. What is a Source Qualifier? What are the tasks we can perform using a Source Qualifier and why it is an ACTIVE transformation?

Source Qualifier Transformation is useful in Mapping, whenever we add relational flat files it is automatically created.

It is an active and connected transformation that represents those rows which are read by integration service.

The Source Qualifier represents the rows that the Informatica Server reads when it executes a session.

•  We can configure the SQ to join [Both INNER as well as OUTER JOIN] data originating from the same source database.
•  We can use a source filter to reduce the number of rows the Integration Service queries.
•  We can specify a number for sorted ports and the Integration Service adds an ORDER BY clause to the default SQL query.
•  We can choose Select Distinct option for relational databases and the Integration Service adds a SELECT DISTINCT clause to the default SQL query.
•  Also we can write Custom/Used Defined SQL query which will override the default query in the Source Qualifier by changing the default settings of the transformation properties for relational databases.
•  Also we have the option to write Pre as well as Post SQL statements to be executed before and after the Source Qualifier query in the source database.
  Since the transformation provides us with the property Select Distinct, when the Integration Service adds a SELECT DISTINCT clause to the default SQL query, which in turn affects the number of rows returned by the Database to the Integration Service and hence it is an Active transformation.

1)When two tables from the same source database with primary key – foreign key transformation relationship is there then the sources can be linked to one source qualifier transformation.
2)Filtering rows when Integration service adds a where clause to the user’s default query.
3)When a user wants an outer join instead of an inner join, then join information is replaced by metadata specified in SQL query.


When the data we need to filter is not a relational source then the user should use Filter transformation. It helps the user to meet specified filter condition to let go or pass through. It will directly drop the rows that do not meet the condition and multiple conditions can be specified.

It determines the way in which the data is fetched from the source and is automatically added while adding a source to mapping.

The list of different tasks where source qualifier is used is as follows:

• Rows filtering
• Data sorting
• Custom query creation
• Joining tables from the same source
• Selecting distinct values

need to connect the source qualifier transformation to the relational or flat file definition in a mapping. The source qualifier transformation converts the source data types to the Informatica native data types. So, you should not alter the data types of the ports in the source qualifier transformation.

The source qualifier transformation can be used to perform the following tasks:

• Joins: You can join two or more tables from the same source database. By default, the sources are joined based on the primary key-foreign key relationships. This can be changed by explicitly specifying the join condition in the “user-defined join” property.
• Filter rows: You can filter the rows from the source database. The integration service adds a WHERE clause to the default query.
• Sorting input: You can sort the source data by specifying the number for sorted ports. The integration service adds an ORDER BY clause to the default SQL query
• Distinct rows: You can get distinct rows from the source by choosing the “Select Distinct” property. The integration service adds a SELECT DISTINCT statement to the default SQL query.
• Custom SQL Query: You can write your own SQL query to do calculations.
•  

138. Why is it that in some cases, SQL override is used?

• The Source Qualifier provides the SQL Query option to override the default query.
• You can enter any SQL statement supported by your source database.
• You might enter your own SELECT statement, or have the database perform aggregate calculations, or call a stored procedure or stored function to read the data and perform some tasks.

139.What Are The Basic Needs To Join Two Sources In A Source Qualifier?

Basic need to join two sources using source qualifier:

1. Both sources should be in same database
2. The should have at least one column in common with same data types

140. What happens to a mapping if we alter the data types between Source and its corresponding Source Qualifier?
The Source Qualifier transformation displays the Informatica data types. The transformation data types determine how the source database binds data when the Integration Service reads it.
Now if we alter the data types in the Source Qualifier transformation or the data types in the Source definition and Source Qualifier transformation do not match, the Designer marks the mapping as invalid when we save the mapping.

141. Suppose we have used the Select Distinct and the Number of Sorted Ports property in the Source Qualifier and then we add Custom SQL Query. Explain what will happen.
Whenever we add Custom SQL or SQL override query it overrides the User-Defined Join, Source Filter, Number of Sorted Ports, and Select Distinct settings in the Source Qualifier transformation. Hence only the user defined SQL Query will be fired in the database and all the other options will be ignored.

142. Describe the situations where we will use the Source Filter, Select Distinct and Number of Sorted Ports properties of Source Qualifier transformation.

Source Filter option is used basically to reduce the number of rows the Integration Service queries, so as to improve performance.
Select Distinct option is used when we want the Integration Service to select unique values from a source.
Filtering out unnecessary data earlier in the data flow, will improve performance.
Number Of Sorted Ports option is used when we want the source data to be in a sorted fashion, so as to use the same in some following transformations like Aggregator or Joiner, those when configured for sorted input will improve the performance.

143. What will happen if the SELECT list COLUMNS in the Custom override SQL Query and the OUTPUT PORTS order in Source Qualifier transformation do not match?
Mismatch or changing the order of the list of selected columns in the SQL Query override of Source Qualifier to that of the connected transformation output ports may result is unexpected value result for ports if data types matches by chance, else will lead to session failure.

144. What happens if in the Source Filter property of SQ transformation we include keyword WHERE say, WHERE CUSTOMERS.CUSTOMER_ID > 1000.
We use Source filter to reduce the number of source records. If we include the string WHERE in the source filter, the Integration Service fails the session. In the above case, the correct syntax will be CUSTOMERS.
CUSTOMER_ID > 1000

145. Describe the scenarios where we go for Joiner transformation instead of Source Qualifier transformation.

While joining Source Data of heterogeneous sources as well as to join flat files we will use the Joiner transformation.
Use the Joiner transformation when we need to join the following types of sources:

•  Join data from different Relational Databases.
•  Join data from different Flat Files.
•  Join relational sources and flat files.

146. What is the maximum number we can use in Number of Sorted Ports for Sybase source system?
Sybase supports a maximum of 16 columns in an ORDER BY clause. So if the source is Sybase, do not sort more than 16 columns.

147. What is use of Source Qualifier in Informatica? Can we create a mapping without a source qualifier?
Source Qualifier is used to convert the data types of Heterogeneous Source Objects supported by Informatica to Native Informatica data types, after which Informatica processes the following objects in a mapping with consistent Informatica data types.
Also for relational table Source Qualifier helps to join multiple tables from the same database and also allows doing Pre or Post SQL operations.
We cannot create a mapping without Source Qualifier; it is the first transformation in Informatica that is attached with the source tables or source flat file instance.

148.Suppose we have two tables of same database type, residing in different Database instance. If a Database Link is available, how can we join the two tables using a Source
Qualifier in Informatica provided there are valid join columns.

Source Qualifier Override:-
SELECT e.empno, e.ename, s.salary, s.comm
FROM emp e, sal@dblinkname s
WHERE e.empno=s.empno
It is advisable to create a Public Synonym at Database for the remote tables so that we can avoid using the syntax : TableName@DBLinkName

149. What is the meaning of “output is deterministic” property in source qualifier transformation?
Output is deterministic means we are informing Informatica that the output does not change (for the same input) across every session run. Why is this required? Consider the source is relational and we have enabled the session for recovery. The session fails and we resume the sessionase if we have set the source as deterministic, then the session would have created a cache (on the disc) of the source during normal run to be used for recovery. This saves time during recovery because we need not issue the SQL command to the source database again.
If this was not set, then the source data cache is not created during normal run and SQL will be reissued during recovery. In some cases, if this property is not set you will not be able to enable recovery for the session.

150.Scenario Implementation 1
How to delete duplicate rows present in relational database using Informatica? Suppose we have duplicate records in Source System and we want to load only the unique records in the Target System eliminating the duplicate rows. What will be the approach?

Assuming that the source system is a Relational Database, to eliminate duplicate records, we can check the Distinct option of the Source Qualifier of the source table and load the target accordingly.