Re: PL/SQL Question

From: <geoff_miller_at_my-deja.com>
Date: Tue, 19 Sep 2000 22:39:24 GMT
Message-ID: <8q8pup$ns1$1_at_nnrp1.deja.com>


In article <_tsx5.53$eX6.16745_at_nnrp2.sbc.net>,   mGOplexAWAY_at_swbell.net wrote:
> I am attempting to create a PL/SQL procedure and I am trying to see
 if there
> is a way of accomplishing what I am trying to do without having to
 write a
> very long IF THEN ELSE statement.
>
> TABLE A has the following fields ...
>
> code_1 VARCHAR2(1)
> code_2 VARCHAR2(1)
> ...
> code_7 VARCHAR2(1)
> amt_1 NUMBER(7,2)
> amt_2 NUMBER(7,2)
> ...
> amt_7 NUMBER(7,2)
>
> TABLE B has the fields like ...
> cl_fee NUMBER(7,2)
> gi_fee NUMBER(7,2)
> fl_fee NUMBER(7,2)
> ...
> zz_fee NUMBER(7,2)
>
> In the code_x fields there can be a value between '1' and '9'. For
 each
> record, it can be completely different. Depending on the value in each
> code_x field, I need to update the fee fields in Table B with the
> corresponding amount from the amount_x fields in Table A.
>
> For example,
>
> Table A
> -----------
> code_1 = 4 amt_1 = 120.00
> code_2 = 1 amt_2 = 175.00
> code_3 = 2 amt_3 = 100.00
>
> If '4' corresponds to fl_fee, and '1' corresponds to cl_fee and '2'
> corresponds to zz_fee then I need to update TABLE B such that
>
> cl_fee = 175.00
> fl_fee = 120.00
> zz_fee = 100.00
>
> With 7 buckets that can have 9 possible values, I am thinking that I
 would
> need 63 different IF conditions to cover all possibilities. Does
 anyone
> know if a more efficient way of accomplishing this than to write all
 those
> IFs?
>
> TIA
>
> David
>

Ummm ... is it too late to suggest that you rethink the table structure? Without going into detail, my experience has been that whenever you find that your processing logic becomes complicated it's time to step back and have another look at how you have structured the data. I would be inclined to split table A up so that you have one record for each amount, and I think this would make it much easier to process; however, without knowing more details of the application I'm not sure if this is appropriate.

Geoff

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 20 2000 - 00:39:24 CEST

Original text of this message