Hello,
I have two tables rates (a) and transactions (b).
For every b.FS, I want to find out new amount (b.amt times a.rate for the matching FS row in b and if a.na is valid based on the exclude flag and the range defined in table a).
It is lot easier to explain using an example.
So here we go....
Exclude_flag = E (exclude): for b.fs = 433638, b.na=80000. I have 2 rows in table a for this fs. Both have exclude_flag of E (exclude). I want the query to go over all the rows for this FS in the table a and return me only one row in the result if and only if b.na falls out of range of a.na_min_value and a.na_max_values. In this example it falls outside of the excluded range for both rows (first two rows of table a).
similarly, exclude_flag = I (Include). for b.fs = 432828, b.na=17200. I have 2 rows in table a for this fs. Both have exclude_flag of I (include). I want the query to go over all the rows for this FS in the table a and return me only one row in the result if and only if b.na falls in the range of a.na_min_value and a.na_max_values. In this example it falls in the range of both the include range for both rows (row 6 and 7 of table a).
The following query, gives me two rows for each b.fs. Is it possible to achieve what I am looking for just using sql (possibly writing analytic function?) or do I need to write pl/sql routine for this?
WITH rates_table
AS ( select 'E' include_exclude_flag,'81000' na_min_value,'81999' na_max_value, '433638' FS, 0.8 rate from dual
UNION
select 'E' include_exclude_flag,'84000' na_min_value,'84999' na_max_value, '433638' FS, 0.8 rate from dual
UNION
select 'I' include_exclude_flag,'12000' na_min_value,'12999' na_max_value, '432828' FS, 0.25 rate from dual
UNION
select 'I' include_exclude_flag,'13000' na_min_value,'13999' na_max_value, '432828' FS, 0.25 rate from dual
UNION
select 'I' include_exclude_flag,'15000' na_min_value,'15000' na_max_value, '432828' FS, 0.25 rate from dual
UNION
select 'I' include_exclude_flag,'16100' na_min_value,'18000' na_max_value, '432828' FS, 0.25 rate from dual
UNION
select 'I' include_exclude_flag,'17100' na_min_value,'18000' na_max_value, '432828' FS, 0.25 rate from dual
UNION
select 'I' include_exclude_flag,'02440' na_min_value,'02470' na_max_value, '016532' FS, 0.35 rate from dual
UNION
select 'E' include_exclude_flag,'21000' na_min_value,'21000' na_max_value, '200020' FS, 0.35 rate from dual ),
transaction_table AS
(select '433638' FS, '80000' NA, 300 amt from dual
union
select '432828' FS, '17200' NA, 500 amt from dual
)
select * from rates_table a, transaction_table b
where 1=1
and ( ( b.na NOT between a.na_min_value and a.na_max_value and a.include_exclude_flag = 'E') OR (
b.na between a.na_min_value and a.na_max_value and a.include_exclude_flag = 'I'))
and b.fs = a.fs
;
Any help is greatly appreciated. I am using oracle 11i