Join or Lookup? Handling predicates in user-defined SQL.

[navistar]

DSFriends,

I have a situation that needs to be handled as follows...

Input(s):
Link1 - Oracle Enterprise stage which selects from TableA with col1 as Key column

Code: Select all

SQL : Select col1 from TableA

Link2 - Another Oracle Enterprise stage with a user-defined SQL accessing data from Table B having a 'WHERE' clause comparing a field from TableB with col1 (key column) from TableA.

Code: Select all

SQL: Select col-x, col-y from TableB where col-x = TableA.col1

Output(s):
In the output, I would like to see all the records from Link2 which has a match on col1 of TableA.

Here are my specific questions:

1. What processing stage would I be using? (Join or LookUp or something else)?

2. If Lookup stage is the answer, which Link would I designate as Primary link and which link would be the Reference link?

How would I define the SQL statement in Link2? I tried as follows using a LookUp stage with TableB as reference stage:

Code: Select all

Select col-x, col-y from TableB where col-x = ?

[/i]The idea was to have for each record from TableA, hit TableB using col1. The '?' was supposed to do this. But I get an Oracle error -911 (Invalid identifier).

Any suggestions that you may have here would be welcome.

Thank You.

[ray.wurlod]

You need numbered parameter markers for Oracle.

Code: Select all

Select col-x, col-y from TableB where col-x = :1