| Control Structures [message #36212] |
Sat, 10 November 2001 19:53  |
siobhancasey
Messages: 1
Registered: November 2001
|
Junior Member |
|
|
Hi,
I am using sqlldr to populate a temp table and need to use the values int two columns to decide what value should be assigned to another column in the same table. I can not figure out which is the best way to code this – can I do it in the .ctl file or should I do it in a stored procedure which I am already using? Here is an example of my attempt at the logic I need to use, but as I’m new to this I imagine there might be a much more efficient algorithm to use.
Any help would be much appreciated…. thks
IF v_rec.system_id_c = 'A'
THEN
IF v_rec.customer_class_code_c = 'B' THEN var_asub_type_n := 2;
ELSIF
v_rec.customer_class_code_c = 'C' THEN var_asub_type_n := 3;
ELSIF
v_rec.customer_class_code_c = 'I' THEN var_asub_type_n := 6;
ELSIF
v_rec.customer_class_code_c = 'P' THEN var_asub_type_n := 4;
ELSE var_asub_type_n := 10;
END IF;
ELSIF v_rec.system_id_c = 'C'
THEN
IF v_rec.customer_class_code_c = 'B' THEN var_asub_type_n := 12;
ELSIF
v_rec.customer_class_code_c = 'C' THEN var_asub_type_n := 23;
ELSIF
v_rec.customer_class_code_c = 'I' THEN var_asub_type_n := 16;
ELSIF
v_rec.customer_class_code_c = 'P' THEN var_asub_type_n := 14;
ELSE var_asub_type_n := 20;
END IF;
ELSIF v_rec.system_id_c = 'B'
THEN
IF v_rec.customer_class_code_c = 'A' THEN var_asub_type_n := 22;
ELSIF
v_rec.customer_class_code_c = 'B' THEN var_asub_type_n := 1;
ELSIF
v_rec.customer_class_code_c = 'C' THEN var_asub_type_n := 7;
ELSIF
v_rec.customer_class_code_c = 'D' THEN var_asub_type_n := 8;
ELSIF
v_rec.customer_class_code_c = 'E' THEN var_asub_type_n := 5;
ELSE var_asub_type_n := 9;
END IF;
ELSIF v_rec.system_id_c = 'D'
THEN
IF v_rec.customer_class_code_c = 'A' THEN var_asub_type_n := 13;
ELSIF
v_rec.customer_class_code_c = 'B' THEN var_asub_type_n := 11;
ELSIF
v_rec.customer_class_code_c = 'C' THEN var_asub_type_n := 17;
ELSIF
v_rec.customer_class_code_c = 'D' THEN var_asub_type_n := 18;
ELSIF
v_rec.customer_class_code_c = 'E' THEN var_asub_type_n := 15;
ELSE var_asub_type_n := 19;
END IF;
ELSE var_asub_type_n := 99;
ENDIF;
----------------------------------------------------------------------
|
|
|
|
| Re: Control Structures [message #36222 is a reply to message #36212] |
Mon, 12 November 2001 05:23  |
hello
Messages: 17
Registered: November 2001
|
Junior Member |
|
|
As far as i know u cannot incorporate this logic in .ctl file but in the procedure u can use decode statement instead of these many IF clauses.
u can say
if v_rec.system_id_c = 'A' then
var_asub_type_c := decod(v_rec.customer_class_code,'B',2,'C',3)
etc.
elsif v_rec.system_id_c = 'B' then
var_asub_type_c := decod(v_rec.customer_class_code,'B',2,'C',3)
end if;
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
