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
Two input lookup parameters within a query and subquery
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.