DSXchange

Here I am using ORAOCI8 as Lookup and Retrieving a Column and This Retrieved column is tranf. to Target. This is my following Query.

SELECT d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.location_id = (SELECT location_id FROM locations WHERE country_id = 'US');

Here Employees is Master table and Departments is Lookup table. Im having Mastertable department_id.

Can any1 help me out to this Query? Thanx in Advance. Here I have tried to pass this query as User-defined SQL option in ORAOCI8 stage. But Im unable to do this.

Ravi.

You subquery is returning more than one value so you need to replace the = with an IN. A single department_id cannot equal a list of values but it can be IN a list of values:
SELECT d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.location_id IN (SELECT location_id FROM locations WHERE country_id = 'US');

However I don't think you need a subquery here, I think the following query will do the same thing but should run a wee bit faster:
SELECT d.department_name
FROM employees e, departments d, location l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = 'US';

Vincent McBurney
Data Integration Services
www.intramatix.com

Hey Vincent,

Thanx your repsond. But here Im using Lookup. So Lookup inside Lookup is not possible right? Without using SQL Query, Is there any other way should perform this Query? If suppose I've Nested In-line queries then how can i do with ORAOCI8?

Thanx in Advance.

Ravi

For a user-defined lookup, you need to setup the sql up a little different from what you are used to. All key columns *plus* the result must be returned by the query, and you need to use the markers DS is expecting for the key values. You also should ensure that your query only returns a single row. Sometimes you can get away with returning multiple rows and you'll only get the first one, other times your jobs will mysteriously abort. Best not to chance it. [:D]

So (guessing here), if you need to do a lookup on "location_id" in your data row, try something like this:

select d.location_id, d.department_name
from departments d
where d.location_id = :1

Where location_id is defined as the first column in your lookup metadata (:1) and is marked as a key. Setup department_name as the second column, not as a key.

Hope this helps. You probably won't be able to use it directly but the example should give you some ideas on where to go. BTW, I removed the employees table from the query as it really didn't seem to be needed and all it probably does is cause duplicate department names to be returned. Are you sure you really need it? Especially considering it is supposed to be a lookup query, returning a singleton result for each specific data row coming in? [?]

-craig

Hey Craig,

But actually my query like this only. So Its okay to given your Query. But Here I have In-line Query. So How to I take it in ORAOCI8? So this is the problem. And I have taken this whole query to my Master Stage (ORAOCI for Employees) and I tried. Then Im getting result. But thats not the way. So that I have to find like this only. Can you say other solution? Bcos this is Inline Query. So in Lookup table i did not get result.

SELECT d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.location_id = (SELECT location_id FROM locations WHERE country_id = 'US');