PX Ochestrate Lookup

dtatem · Post by **dtatem** » Fri May 25, 2007 12:48 pm

From an input source file, have 5 columns that are key columns plus a starting range value. I am attempting to perform a Sparse DB2 lookup using the columns above...the job fails with An input record has no lookup match. Now if I make the necessary changes and submit into DB2, the query works. Am I missing something? Thanks in Advance

Here is the DB2 sparse query:
SELECT
PRC_AMT
FROM
SCHEMA.AMT_PRC_TBL
WHERE
(C1 = ORCHESTRATE.C1
AND C2 = ORCHESTRATE.C2
AND C3 = ORCHESTRATE.C3
AND C4 = ORCHESTRATE.C4
AND C5 = ORCHESTRATE.C5) AND (ORCHESTRATE.AMT BETWEEN STR_RNG and END_RNGE_NBR)

ray.wurlod · Post by **ray.wurlod** » Fri May 25, 2007 2:10 pm

What is your "lookup failed" rule in the Lookup stage? By default it's set to Fail, which causes your job to abort on the first occasion a lookup fails to return a row.

dtatem · Post by **dtatem** » Fri May 25, 2007 2:15 pm

Thanks for replying... The default is set to Fail, I changed it to "Continue" but it runs OK doesnot fail, but all of my results from the Lookup is "NULL"

If I take the SQL and substitute values and run in DB2 I get results.

How should I declare columns C1 - C5? Do I define them as key columns in DataStage. These columns are what are in my source data and they are also in the DB2 table. The AMT value is in my source also

Thanks..

dtatem

ray.wurlod · Post by **ray.wurlod** » Fri May 25, 2007 3:00 pm

You need to declare them as Key on the DB2 link. "Key" in this context means "lookup key" rather than "primary key".

dtatem · Post by **dtatem** » Sun May 27, 2007 9:51 pm

Ray:

Thanks for replying.
My columns C1-C5 are declared as "Key" for lookup keys and I am still getting the same results ."job fails with An input record has no lookup match"
Any other ideas and/or suggestions?

Thanks

dtatem

JoshGeorge · Post by **JoshGeorge** » Sun May 27, 2007 10:33 pm

You might want to format/trim the input link data before look up.

ray.wurlod · Post by **ray.wurlod** » Sun May 27, 2007 11:09 pm

The variants on the "lookup failed" rule indicate that you are getting no matches. The Continue rule mimics a left outer join, hence the returned NULL fields.

Can you execute this query successfully outside of DataStage with appropriate value substitutions?

sanjay · Post by **sanjay** » Sun May 27, 2007 11:20 pm

dtatem

Try wiith just C1 in your query i.e.

SELECT
PRC_AMT
FROM
SCHEMA.AMT_PRC_TBL
WHERE
C1 = ORCHESTRATE.C1
see whetther it returns value then GO ON ADDING C2,C3,C4,C5

Sanjay

ray.wurlod wrote:The variants on the "lookup failed" rule indicate that you are getting no matches. The Continue rule mimics a left outer join, hence the returned NULL fields.

Can you execute this query successful ...