Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(1 Rating)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Return the values of the Input ports of a SQL Transformation when no rows are returned by the result set
Solution
In this example CUSTOMER_ID is linked to a SQL Transformation. The SQL Transformation will return all the records for which CUSTOMER_ID is matched. However, it does not return anything if no match is found and it is expected behavior of SQL Query.
To pass the value of the CUSTOMER_ID to the next transformation even if no match is found modify the SQL Transformation as follows:
  1. Select the SQL Ports tab in the SQL Transformation.

  2. Add a query similar to following:

    SELECT CUSTOMER_ID,FIRST_NAME FROM CUSTOMERS WHERE CUSTOMER_ID=?CUSTOMER_ID?;
    SELECT ?CUSTOMER_ID? where (select count(*) FROM CUSTOMERS WHERE CUSTOMER_ID=?CUSTOMER_ID?) = 0

This executes two queries. When the first query does not return any rows the second query will return the CUSTOMER_ID.

A sample mapping is attached.

More Information
Reference
Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter 8.1.1
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:8/2/2008 5:08 PMID:21080
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)