Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Create a SQL override based on tables from more than one database
Problem Description

How to create a SQL override based on tables from more than one database.

Example:

A mapping has two sources in different databases.
The query to be run in the session must include a WHERE clause containing a Date.
If both the source data table and the table containing the date value were in the same database you would use SQL like this:

SELECT C1, C2, ORDER_DATE FROM ORDERS WHERE ORDER_DATE > (SELECT FILTER_DATE FROM EXTRACT_HISTORY)
But, in this case the ORDERS table and EXTRACT_HISTORY table are physically located in different database servers.
Cause
Solution

Here is a suggested procedure that creates a dynamic view using two pre-session commands when a session is executed.
Note that the syntax in this example is Oracle specific.

1. Create a mapping that will extract the desired date to a flat file.

The string output to the flat file ( F2 in this example the) will be a string like:

'12311999','mmddyyyy');
This string will be used by the real session to concatenate at the end of the create view script.
Use the following format for variable port and output ports to create the correct flat file. So VAR_1 is the variable port and OUT_1 is the output port. This is for the expression transformation mapping that will take the date and put it into the flat file.
VAR_1 = TO_CHAR(DATEPORT, 'MMDDYYYY') = 12311999
OUT_1 = CHR(96) || V1 || CHR(96) || ',' || CHR(96) ||'MMDDYYYY' || CHR(96) || ');'

2. Create a SQL script that will create the view ("F1" in this example).

The source in the mapping must have the same name as the view created by this script (F1):

CREATE OR REPLACE VIEW CONTENT_VIEW AS SELECT * FROM CONTENT
WHERE ORDER_DATE > TO_DATE(

3. Run the session with the two pre-session commands.

Pre-session command 1:

cat /usr/etc/f1 /user/etc/f2 > /usr/etc/f3.sql

This will concatenate the output from the first file F1 onto the end of the create view script F2 and will create a new file F3.sql . The newly created SQL script file F3.sql will be executed by pre-session command 2 in the Oracle instance and actually create the view.

Pre-session command 2:

sqlplus scott/tiger@dbalias @/usr/etc/f3.sql

More Information
Applies To
Product: PowerCenter
Problem Type:
User Type: Developer
Project Phase:
Product Version: PowerCenter
Database: Oracle
Operating System:
Other Software:

Reference
Attachments
Last Modified Date:3/12/2010 3:55 PMID:11298
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)