Two input lookup parameters within a query and subquery

[Anand K]

Hi All

I wanted to use following query as User defined query in Oracle Source Stage with two input lookup parameters. I would like to know, Can I pass two values from other source stage (CASE_KEY and TR_CD in the following example)

SELECT DISTINCT FDRT_KEY FROM FD_RATES WHERE
BNTP_KEY =
(
SELECT DISTINCT A.BNTP_KEY FROM BEN_TYPES A
WHERE
CASE_KEY = :1 AND
SBNT_KEY = 90
) AND
TR_CD = :2

Any help would be highly appreciated. :D

Thanks in advance
Anand

[ray.wurlod]

Yes, you can do this legally.

You need to specify that the A.CASE_KEY and FD_RATES.TR_CD as "key" columns (even if this is a lie) so that you can supply reference key expressions to them.

In turn, this means that A.CASE_KEY must appear somehow in the outer query.

Why not load the inner query into a hashed file, and use that for a lookup? This should be OK, because your nested query isn't using a correlated sub-query.