Avoid hardcoding in select clause

Question

I have the below query

 SELECT decode(detl_cd,'AAA',trunc(amt * to_number(pct) / 100,2),                                                            
                               'BBB',trunc(amt * to_number(pct) / 100,2),                                                            
                               'CCC',(amt-trunc(amt * to_number(pct) / 100,2))
    INTO trans_amount
    FROM dual;

I get the value detl_cd from a cursor and amt from an input file.

Select tb1.id, tb2.detl_cd,tb2.pct 
  from tb1
  join tb2 on tb1.agent_code=tb2.agent_code
 where tb1.id='1';

Each id has 3 detl cd's and each detl code has different calculation. How to avoid hardcode in decode. Creating a table is not an option.

Input file

ID Amount
1  1000
2  2500
3  350

Id 1 & 2 belong to a group that is assigned 3 different detl cd's and different precentage(pct).

output file

ID Detl_cd Amount
1  AAA1     250
1  BBB1     250
1  CCC1     750
2  AAA3     625
2  BBB3     625
2  CCC3    1875
3           350

Each ID has 3 different detl_cd's but the calculation for AAA1 and AAA2 are the same so is BBB & CCC.

Answer 1

I guess you may want

 WITH DATA AS 
(Select tb1.id , tb2.detl_cd,tb2.pct 
 from tb1
  join tb2 on tb1.agent_code=tb2.agent_code
  where tb1.id='1') 
 SELECT decode(detl_cd,'AAA',trunc(amt * to_number(pct) / 
                               100,2),                                                            
                           'BBB',trunc(amt * to_number(pct) / 100,2),                                                            
                           'CCC',(amt-trunc(amt * to_number(pct) / 100,2))
 as trans_amount 
 FROM Data;

Answer 2

Creating a table is not an option.

You want a solution which stores a set of business rules without specifying the business rules in the code. But also without creating a table to store those rules.

That only leaves a user-defined function.

create or replace function calc_amount 
  ( p_detl_cd in varchar2
   ,p_amt     in number  
   ,p_pct     in number    )  
  return number
as
begin
  case substr(p_detl_cd, 1, 3) 
    when 'AAA' then return trunc(p_amt * to_number(p_pct) / 100,2);                                                            
    when 'BBB' then return trunc(p_amt * to_number(p_pct) / 100,2);                                                           
    when 'CCC' then return (p_amt-trunc(p_amt * to_number(p_pct) / 100,2);
  end case;
end calc_amount;

You would call this function in SQL or in PL/SQL. You are a bit vague regarding tables and files, so I'm not really clear where the data comes from, but it might look something like this in PL/SQL:

trans_amount := calc_amount(detl_cd, amt, pct);

---

I am looking to avoid hardcoding of 'AAA' as these codes may change/replaced in future and i do not want a rework

Or the codes may change the same and the calculations change. Doesn't matter. The hard truth is, you have to hard code the codes and their associated rules somewhere. It is impossible to have an infinitely flexible, soft coded system.

A table is the easiest thing to maintain, and that offers you the most flexibility. But you need to use dynamic SQL or a function to apply the calculation; a function would be my preference. The worst solution would be to have the codes and calculations in an external configuration file which you load at the same time as the input file.

Alternatively, try to put a value on "may change". How likely is it that the codes (or calculations) will change? How often? Do the maths and maybe you'll discover that change is unlikely or very infrequent, and the simplest option is to stick with that decode and take the hit of rework should the occasion arise.

---

Incidentally, are you sure you mean trunc() and not round()?