Home » SQL & PL/SQL » SQL & PL/SQL » plsql logic (merged 3)
| plsql logic (merged 3) [message #649561] |
Thu, 31 March 2016 02:38  |
 |
yashi7777
Messages: 42
Registered: March 2016
|
Member |
|
|
Hi,
I have 2 input columns
1st column has string values like
TPOD~XYZ~XERV~MMRR~ ~ ~ ~ ~ ~ ~ ~ ~ ~
2nd column has like
GRUNT~BICHI~POLDA~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
My output from above would be
(TPOD-GRUNT)-XYZ-(BICHI-XERV)-(POLDA-MMRR)
XYZ is always a value I would have common in column one so it does not have a pair.But the trend is always
1st value of Field 1 then '-' then 1st value of Field 2 then '-' then 2nd value of Field 1 ...and then it stops since we cannot proceed.
I can create the above, but now I have a 3rd column like
K~L~1~2~ ~ ~ ~ ~ ~ ~ ~ ~ ~ or it can be
R_S_1~J
Now 3rd column matches the number of entries in 1st column
meaning
K or R =TPOD
L or S =XYZ
1 =XERV
2 or J= MMRR
The requirement is if I see K in 3 rd column I will have to get the output as
XYZ-(BICHI-XERV)-(POLDA-MMRR) ---this is my answer
K=(TPOD-GRUNT) --I need to report this value also
if I see J in 3 rd column I will have to get the output as
(TPOD-GRUNT)-XYZ-(BICHI-XERV) ---this is my answer
J=(POLDA-MMRR) ---I need to report this value also
say for example if I have both K and J in column 3 like K~L~J~2 my output would be
XYZ-(POLDA-MMRR)
Can you please help me how to resolve this.
|
|
|
|
|
|
|
|
|
|
| Re: plsql logic required [message #649591 is a reply to message #649590] |
Thu, 31 March 2016 08:56  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
SET define OFF;
--SQL Statement which produced this data:
--
-- select * from test;
--
INSERT INTO test
(inputfield1,
inputfield2,
inputfield3,
output1,
output2,
output3)
VALUES ('K~D~L~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'JB ~XYZ~TYFS~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'FAY~KFD~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'(JB-FAY)-XYZ-(KFD-TYFS)',
'XYZ-(KFD-TYFS)',
'(JB-FAY)');
INSERT INTO test
(inputfield1,
inputfield2,
inputfield3,
output1,
output2,
output4)
VALUES ('1~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'XYZ~KOU~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'PODEN~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~'
,
'XYZ-(PODEN-KOU)',
'XYZ',
'(PODEN-KOU)');
INSERT INTO test
(inputfield1,
inputfield2,
inputfield3,
output1,
output2)
VALUES ('R~7~D~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'SRC~XYZ~ACR~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'FLH~ABRD~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'(SRC-FLH)-XYZ-(ABRD-ACR)',
'(SRC-FLH)-XYZ-(ABRD-ACR)');
INSERT INTO test
(inputfield1,
inputfield2,
inputfield3,
output1,
output2,
output4)
VALUES ('G~1~2~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'BNS~XYZ~YSW~RANDI~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'CHG ~BORG~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'(BNS-CHG)-XYZ-(BORG-YSW)-(RANDI)',
'(BNS-CHG)-XYZ-(BORG-YSW)',
'(RANDI)');
INSERT INTO test
(inputfield1,
inputfield2,
inputfield3,
output1,
output2,
output3)
VALUES ('K~S~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'DC ~XYZ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'(DC)-XYZ',
'XYZ',
'(DC)');
INSERT INTO test
(inputfield1,
inputfield2,
inputfield3,
output1,
output2,
output3)
VALUES ('K~S~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'CRX ~XYZ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'CHG ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'(CRX-CHG)-XYZ',
'XYZ',
'(CRX-CHG)');
INSERT INTO test
(inputfield1,
inputfield2,
inputfield3,
output1,
output2,
output4)
VALUES ('R~6~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'VSV ~XYZ~MC ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'POCH ~TAUNT~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~'
,
'(VSV-POCH)-XYZ-(TAUNT-MC)',
'(VSV-POCH)-XYZ',
'(TAUNT-MC)');
INSERT INTO test
(inputfield1,
inputfield2,
inputfield3,
output1,
output2,
output3,
output4)
VALUES ('K~S~1~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'CWR~XYZ~LSR~MRP~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'CHOD~FLIN~PAIN~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~',
'(CWR-CHOD)-XYZ-(FLIN-LSR)-(PAIN-MRP)',
'XYZ-(FLIN-LSR)',
'(CWR-CHOD)',
'(PAIN-MRP)');
COMMIT;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: plsql logic required [message #649602 is a reply to message #649600] |
Thu, 31 March 2016 13:12 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
yashi7777 wrote on Thu, 31 March 2016 19:02Michel,
if you read my statement
"Each value of input field 1 correspond to a value in input field 2. Like for first example: K=JB; D=XYZ;L=TYFS.
Output1 = 1st value of input2 and then 1st value of input3 and they make a pair and then the trend continues. However, when input2='XYZ' , it does not make a pair though"...Look at the table and you will understand hopefully.
Tell me yashi, do you think that Michel responded to you without bothering to read what you had written? Or, do you think, maybe, just maybe, that he has already read what you had said, and your repeating it is utterly pointless as the first reading didn't clarify it, why would the second time of writing it?
Here's and idea, assume that your description is not clear enough and try explaining it again, more detail is probably required, say by explaining each output column for each row.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: plsql logic required [message #649607 is a reply to message #649606] |
Thu, 31 March 2016 18:14 |
|
yashi7777
Messages: 42
Registered: March 2016
|
Member |
|
|
Below is what I tried Barbara,
declare
l_var varchar2(1000) := 'input1';
z_var varchar2(1000) := 'input2';
l_num number;
l_num1 number;
g_var varchar2(1000);
f_var varchar2(1000);
h_var varchar2(1000) :='';
type t is table of varchar2(100);
t1 t;
val varchar2(20);
Begin
t1 := t();
l_num := length(replace(l_var,'~'));
l_num1 := length(replace(z_var,'~'));
for i in 1..l_num loop
g_var := regexp_substr(l_var,'[^~]+',1,i);
f_var := regexp_substr(z_var,'[^~]+',1,i);
t1.extend;
val := (LTRIM(RTRIM(g_var)))||'-'||(LTRIM(RTRIM(f_var)));
t1(i) := val;
end loop;
for i in 1..t1.count loop
if i=1 then
h_var := t1(i);
else
h_var := h_var || '-' ||t1(i);
end if;
end loop;
dbms_output.put_line(RTRIM(h_var,'-'));
End;
/
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Thu, 31 March 2016 19:08] by Moderator Report message to a moderator |
|
|
|
| Re: plsql logic required [message #649608 is a reply to message #649607] |
Thu, 31 March 2016 21:41 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It looks like you want to accept two strings and return another, so the usual way to do that would be using a function. I converted your pl/sql block to a function below and made some corrections that I described in comment lines. I demonstrated how it accepts the values of inputfield2 and inputfield3 from your table and returns the value of output1 that you want. I understand that inputfield1 is apparently the third input column that you referred to, but I can't understand how you derive the values for output2, output3, or output4, so you will need to explain that better, line by line, using the values in the table that you provided.
SCOTT@orcl> create or replace function get_output
2 (input1 in varchar2,
3 input2 in varchar2)
4 return varchar2
5 as
6 l_var varchar2(1000) := input1;
7 z_var varchar2(1000) := input2;
8 l_num number;
9 g_var varchar2(1000);
10 f_var varchar2(1000);
11 h_var varchar2(1000) :='';
12 type t is table of varchar2(100);
13 t1 t;
14 val varchar2(20);
15 -- added offset to use when g_var = 'XYZ':
16 offset number := 0;
17 begin
18 t1 := t();
19 -- smaller l_num, just number of values, number of ~'s:
20 l_num := length(l_var)-length(replace(l_var,'~'));
21 for i in 1..l_num loop
22 g_var := regexp_substr(l_var,'[^~]+',1,i);
23 -- used offset:
24 f_var := regexp_substr(z_var,'[^~]+',1,i-offset);
25 t1.extend;
26 -- added code for when g_var = 'XYZ':
27 if g_var = 'XYZ' then
28 val := (ltrim(rtrim(g_var)));
29 offset := offset + 1;
30 else
31 val := (ltrim(rtrim(g_var)));
32 -- only add hyphen when f_var is not null:
33 if ltrim(rtrim(f_var)) is not null then
34 val := val||'-'||(ltrim(rtrim(f_var)));
35 end if;
36 end if;
37 t1(i) := val;
38 end loop;
39 for i in 1..t1.count loop
40 -- add parentheses only when t1(i) is not 'XYZ':
41 if t1(i) = 'XYZ' then
42 h_var := h_var||t1(i)||'-';
43 else
44 h_var := h_var||'('||t1(i)||')'||'-';
45 end if;
46 end loop;
47 -- did some extra trimming:
48 h_var := rtrim(h_var,'(-)');
49 if substr (h_var,-3,3) != 'XYZ' then
50 h_var := h_var||')';
51 end if;
52 return h_var;
53 end get_output;
54 /
Function created.
SCOTT@orcl> show errors
No errors.
SCOTT@orcl> select inputfield2, inputfield3,
2 get_output (inputfield2, inputfield3)
3 from test
4 /
INPUTFIELD2
--------------------------------------------------------------------------------
INPUTFIELD3
--------------------------------------------------------------------------------
GET_OUTPUT(INPUTFIELD2,INPUTFIELD3)
--------------------------------------------------------------------------------
JB ~XYZ~TYFS~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
FAY~KFD~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(JB-FAY)-XYZ-(TYFS-KFD)
XYZ~KOU~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
PODEN~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
XYZ-(KOU-PODEN)
SRC~XYZ~ACR~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
FLH~ABRD~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(SRC-FLH)-XYZ-(ACR-ABRD)
BNS~XYZ~YSW~RANDI~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CHG ~BORG~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(BNS-CHG)-XYZ-(YSW-BORG)-(RANDI)
DC ~XYZ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(DC)-XYZ
CRX ~XYZ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CHG ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(CRX-CHG)-XYZ
VSV ~XYZ~MC ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
POCH ~TAUNT~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(VSV-POCH)-XYZ-(MC-TAUNT)
CWR~XYZ~LSR~MRP~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CHOD~FLIN~PAIN~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(CWR-CHOD)-XYZ-(LSR-FLIN)-(MRP-PAIN)
8 rows selected.
|
|
|
|
|
|
| Re: plsql logic required [message #649613 is a reply to message #649609] |
Fri, 01 April 2016 01:10 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I think I understand the requirements now. I modified the function to specify whether output1 or ouput2 or ouput3 or output4 is desired and to accept inputfield1, inputfield2, and inputfield3 in order each time, so that the same function can be used 4 times in one query to get all of the outputs. Please see the demonstration below.
SCOTT@orcl> create or replace function get_output
2 (outputnum in number,
3 input1 in varchar2,
4 input2 in varchar2,
5 input3 in varchar2)
6 return varchar2
7 as
8 a_var varchar2(1000) := input1;
9 l_var varchar2(1000) := input2;
10 z_var varchar2(1000) := input3;
11 l_num number;
12 b_var varchar2(1000);
13 g_var varchar2(1000);
14 f_var varchar2(1000);
15 h_var varchar2(1000) :='';
16 type t is table of varchar2(100);
17 t1 t;
18 val varchar2(20);
19 offset number := 0;
20 begin
21 t1 := t();
22 l_num := length(l_var)-length(replace(l_var,'~'));
23 for i in 1..l_num loop
24 b_var := regexp_substr(a_var,'[^~]+',1,i);
25 g_var := regexp_substr(l_var,'[^~]+',1,i);
26 f_var := regexp_substr(z_var,'[^~]+',1,i-offset);
27 t1.extend;
28 if (outputnum = 2 and (b_var = 'K' or b_var = 'J'))
29 or (outputnum = 3 and b_var != 'K')
30 or (outputnum = 4 and b_var != 'J') then
31 val := null;
32 else
33 if g_var = 'XYZ' then
34 val := (ltrim(rtrim(g_var)));
35 else
36 val := (ltrim(rtrim(g_var)));
37 if ltrim(rtrim(f_var)) is not null then
38 val := val||'-'||(ltrim(rtrim(f_var)));
39 end if;
40 end if;
41 end if;
42 if g_var = 'XYZ' then
43 offset := offset + 1;
44 end if;
45 t1(i) := val;
46 end loop;
47 for i in 1..t1.count loop
48 if t1(i) = 'XYZ' then
49 h_var := h_var||t1(i)||'-';
50 elsif t1(i) is not null then
51 h_var := h_var||'('||t1(i)||')'||'-';
52 end if;
53 end loop;
54 h_var := rtrim(h_var,'(-)');
55 if substr (h_var,-3,3) != 'XYZ' then
56 h_var := h_var||')';
57 end if;
58 return h_var;
59 end get_output;
60 /
Function created.
SCOTT@orcl> show errors
No errors.
SCOTT@orcl> select inputfield1, inputfield2, inputfield3,
2 get_output (1, inputfield1, inputfield2, inputfield3) output1,
3 get_output (2, inputfield1, inputfield2, inputfield3) output2,
4 get_output (3, inputfield1, inputfield2, inputfield3) output3,
5 get_output (4, inputfield1, inputfield2, inputfield3) output4
6 from test
7 /
INPUTFIELD1
--------------------------------------------------------------------------------
INPUTFIELD2
--------------------------------------------------------------------------------
INPUTFIELD3
--------------------------------------------------------------------------------
OUTPUT1
--------------------------------------------------------------------------------
OUTPUT2
--------------------------------------------------------------------------------
OUTPUT3
--------------------------------------------------------------------------------
OUTPUT4
--------------------------------------------------------------------------------
K~D~L~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
JB ~XYZ~TYFS~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
FAY~KFD~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(JB-FAY)-XYZ-(TYFS-KFD)
XYZ-(TYFS-KFD)
(JB-FAY)
1~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
XYZ~KOU~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
PODEN~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
XYZ-(KOU-PODEN)
XYZ
(KOU-PODEN)
R~7~D~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SRC~XYZ~ACR~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
FLH~ABRD~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(SRC-FLH)-XYZ-(ACR-ABRD)
(SRC-FLH)-XYZ-(ACR-ABRD)
G~1~2~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~
BNS~XYZ~YSW~RANDI~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CHG ~BORG~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(BNS-CHG)-XYZ-(YSW-BORG)-(RANDI)
(BNS-CHG)-XYZ-(YSW-BORG)
(RANDI)
K~S~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
DC ~XYZ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(DC)-XYZ
XYZ
(DC)
K~S~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CRX ~XYZ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CHG ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(CRX-CHG)-XYZ
XYZ
(CRX-CHG)
R~6~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
VSV ~XYZ~MC ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
POCH ~TAUNT~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(VSV-POCH)-XYZ-(MC-TAUNT)
(VSV-POCH)-XYZ
(MC-TAUNT)
K~S~1~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CWR~XYZ~LSR~MRP~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CHOD~FLIN~PAIN~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(CWR-CHOD)-XYZ-(LSR-FLIN)-(MRP-PAIN)
XYZ-(LSR-FLIN)
(CWR-CHOD)
(MRP-PAIN)
8 rows selected.
[Updated on: Fri, 01 April 2016 01:59] Report message to a moderator |
|
|
|
|
|
|
|
| Re: plsql logic required [message #649639 is a reply to message #649638] |
Fri, 01 April 2016 11:37 |
|
yashi7777
Messages: 42
Registered: March 2016
|
Member |
|
|
Barbara,
I see another issue other than the above. when input1 field count goes beyond 4 the code cannot handle the output logic anymore.
say if insert statement was
Insert into TEST
(INPUTFIELD1, INPUTFIELD2, INPUTFIELD3, OUTPUT1, OUTPUT2,
OUTPUT3, OUTPUT4)
Values
('K~S~1~2~ J~ ~ ~ ~ ~ ~ ~ ~ ~', 'CWR~XYZ~LSR~MRP~ HOG~ ~ ~ ~ ~ ~ ~ ~ ~', 'CHOD~FLIN~PAIN~ LMN ~ ~ ~ ~ ~ ~ ~ ~ ~ ~', '(CWR-CHOD)-XYZ-(FLIN-LSR)-(PAIN-MRP)-(LMN-HOG)', 'XYZ-(FLIN-LSR)-(PAIN-MRP)',
'(CWR-CHOD)', '(LMN-HOG)');
COMMIT;
The output doesnot work per the function you gave me. I mean the output from your function doesnot tally per output in the insert statement.Thanks so much in advance.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: plsql logic required [message #649650 is a reply to message #649638] |
Fri, 01 April 2016 14:34 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
yashi7777 wrote on Fri, 01 April 2016 08:51Barbara,
Sorry I missed something and I realize tht there is a problem in the output1 you provided.So output2, out3, output4 also are not correct .
(JB-FAY)-XYZ-(TYFS-KFD) it should be (JB-FAY)-XYZ-(KFD-TYFS) look at the TYFS-KFD part
in all your output they got affected. Can you check please. If you compare your output with my insert table output you will understand. I tried fixing but somewhere I am stuck.
I previously asked the following, but you do not answer:
Quote:
In your initial example, why is it:
(TPOD-GRUNT)-XYZ-(BICHI-XERV)-(POLDA-MMRR)
and not:
(TPOD-GRUNT)-XYZ-(XERV-BICHI)-(MMRR-POLDA)
Why does it switch to putting the value of the second column before the value of the first column, instead of following the trend of value of the first column followed by value of the second column as you explained?
So, I ask again, in your new example, why should it be (KFD-TYFS) and not (TYFS-KFD)?
It appears to me that your examples where it does that were derived from your erroneous code that did not account for XYZ not having a match and, although it did not have parentheses, it was matching the next value in the second column with the preceding XYZ. Without parentheses, your original code was returning
JB-FAY-XYZ-KFD-TYFS
and if you added parentheses, it would end up:
(JB-FAY)-(XYZ-KFD)-TYFS
So, is there some rule that I have misunderstood or are your examples that do not match your explanation wrong?
[Updated on: Fri, 01 April 2016 14:38] Report message to a moderator |
|
|
|
| Re: plsql logic required [message #649651 is a reply to message #649650] |
Fri, 01 April 2016 14:43 |
|
yashi7777
Messages: 42
Registered: March 2016
|
Member |
|
|
Sorry if I missed it before;
But if you check it is always that after it hits XYZ the order of selection from input3 comes first and input2 comes second....basically if you see due to XYZ getting in into the sequence the partners flip.
See this example:
Insert into TEST
(INPUTFIELD1, INPUTFIELD2, INPUTFIELD3, OUTPUT1, OUTPUT2,
OUTPUT3, OUTPUT4)
Values
('K~S~1~2~ J~ ~ ~ ~ ~ ~ ~ ~ ~', 'CWR~XYZ~LSR~MRP~ HOG~ ~ ~ ~ ~ ~ ~ ~ ~', 'CHOD~FLIN~PAIN~ LMN ~ ~ ~ ~ ~ ~ ~ ~ ~ ~', '(CWR-CHOD)-XYZ-(FLIN-LSR)-(PAIN-MRP)-(LMN-HOG)', 'XYZ-(FLIN-LSR)-(PAIN-MRP)',
'(CWR-CHOD)', '(LMN-HOG)');
COMMIT;
|
|
|
|
| Re: plsql logic required [message #649653 is a reply to message #649639] |
Fri, 01 April 2016 14:50 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
yashi7777 wrote on Fri, 01 April 2016 09:37Barbara,
I see another issue other than the above. when input1 field count goes beyond 4 the code cannot handle the output logic anymore.
say if insert statement was
Insert into TEST
(INPUTFIELD1, INPUTFIELD2, INPUTFIELD3, OUTPUT1, OUTPUT2,
OUTPUT3, OUTPUT4)
Values
('K~S~1~2~ J~ ~ ~ ~ ~ ~ ~ ~ ~', 'CWR~XYZ~LSR~MRP~ HOG~ ~ ~ ~ ~ ~ ~ ~ ~', 'CHOD~FLIN~PAIN~ LMN ~ ~ ~ ~ ~ ~ ~ ~ ~ ~', '(CWR-CHOD)-XYZ-(FLIN-LSR)-(PAIN-MRP)-(LMN-HOG)', 'XYZ-(FLIN-LSR)-(PAIN-MRP)',
'(CWR-CHOD)', '(LMN-HOG)');
COMMIT;
The output doesnot work per the function you gave me. I mean the output from your function doesnot tally per output in the insert statement.Thanks so much in advance.
I see the problem. It has nothing to do with how many. It is caused by the space in front of the J. To eliminate that and any other problems due to those extra spaces, just change that section of the code by adding some extra trimming, as shown below.
b_var := ltrim(rtrim(regexp_substr(a_var,'[^~]+',1,i)));
g_var := ltrim(rtrim(regexp_substr(l_var,'[^~]+',1,i)));
f_var := ltrim(rtrim(regexp_substr(z_var,'[^~]+',1,i-offset)));
|
|
|
|
| Re: plsql logic required [message #649654 is a reply to message #649651] |
Fri, 01 April 2016 15:06 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
yashi7777 wrote on Fri, 01 April 2016 12:43Sorry if I missed it before;
But if you check it is always that after it hits XYZ the order of selection from input3 comes first and input2 comes second....basically if you see due to XYZ getting in into the sequence the partners flip.
See this example:
Insert into TEST
(INPUTFIELD1, INPUTFIELD2, INPUTFIELD3, OUTPUT1, OUTPUT2,
OUTPUT3, OUTPUT4)
Values
('K~S~1~2~ J~ ~ ~ ~ ~ ~ ~ ~ ~', 'CWR~XYZ~LSR~MRP~ HOG~ ~ ~ ~ ~ ~ ~ ~ ~', 'CHOD~FLIN~PAIN~ LMN ~ ~ ~ ~ ~ ~ ~ ~ ~ ~', '(CWR-CHOD)-XYZ-(FLIN-LSR)-(PAIN-MRP)-(LMN-HOG)', 'XYZ-(FLIN-LSR)-(PAIN-MRP)',
'(CWR-CHOD)', '(LMN-HOG)');
COMMIT;
I see. Please see the comment lines explaining the changes that I made to the code below.
SCOTT@orcl> create or replace function get_output
2 (outputnum in number,
3 input1 in varchar2,
4 input2 in varchar2,
5 input3 in varchar2)
6 return varchar2
7 as
8 a_var varchar2(1000) := input1;
9 l_var varchar2(1000) := input2;
10 z_var varchar2(1000) := input3;
11 l_num number;
12 b_var varchar2(1000);
13 g_var varchar2(1000);
14 f_var varchar2(1000);
15 h_var varchar2(1000) :='';
16 type t is table of varchar2(100);
17 t1 t;
18 val varchar2(20);
19 offset number := 0;
20 -- added variable:
21 temp_var varchar2(1000);
22 begin
23 t1 := t();
24 l_num := length(l_var)-length(replace(l_var,'~'));
25 for i in 1..l_num loop
26 b_var := ltrim(rtrim(regexp_substr(a_var,'[^~]+',1,i)));
27 -- move offset from f_var to g_var:
28 g_var := ltrim(rtrim(regexp_substr(l_var,'[^~]+',1,i-offset)));
29 f_var := ltrim(rtrim(regexp_substr(z_var,'[^~]+',1,i)));
30 t1.extend;
31 if (outputnum = 2 and (b_var = 'K' or b_var = 'J'))
32 or (outputnum = 3 and b_var != 'K')
33 or (outputnum = 4 and b_var != 'J') then
34 val := null;
35 else
36 if g_var = 'XYZ' then
37 val := (ltrim(rtrim(g_var)));
38 else
39 val := (ltrim(rtrim(g_var)));
40 if ltrim(rtrim(f_var)) is not null then
41 val := val||'-'||(ltrim(rtrim(f_var)));
42 end if;
43 end if;
44 end if;
45 if g_var = 'XYZ' then
46 offset := offset + 1;
47 -- after XYZ, switch l_var and z_var:
48 temp_var := l_var;
49 l_var := z_var;
50 z_var := temp_var;
51 end if;
52 t1(i) := val;
53 end loop;
54 for i in 1..t1.count loop
55 if t1(i) = 'XYZ' then
56 h_var := h_var||t1(i)||'-';
57 elsif t1(i) is not null then
58 h_var := h_var||'('||t1(i)||')'||'-';
59 end if;
60 end loop;
61 h_var := rtrim(h_var,'(-)');
62 if substr (h_var,-3,3) != 'XYZ' then
63 h_var := h_var||')';
64 end if;
65 return h_var;
66 end get_output;
67 /
Function created.
SCOTT@orcl> show errors
No errors.
SCOTT@orcl> select inputfield1, inputfield2, inputfield3,
2 get_output (1, inputfield1, inputfield2, inputfield3) output1,
3 get_output (2, inputfield1, inputfield2, inputfield3) output2,
4 get_output (3, inputfield1, inputfield2, inputfield3) output3,
5 get_output (4, inputfield1, inputfield2, inputfield3) output4
6 from test
7 /
INPUTFIELD1
--------------------------------------------------------------------------------
INPUTFIELD2
--------------------------------------------------------------------------------
INPUTFIELD3
--------------------------------------------------------------------------------
OUTPUT1
--------------------------------------------------------------------------------
OUTPUT2
--------------------------------------------------------------------------------
OUTPUT3
--------------------------------------------------------------------------------
OUTPUT4
--------------------------------------------------------------------------------
K~D~L~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
JB ~XYZ~TYFS~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
FAY~KFD~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(JB-FAY)-XYZ-(KFD-TYFS)
XYZ-(KFD-TYFS)
(JB-FAY)
1~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
XYZ~KOU~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
PODEN~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
XYZ-(PODEN-KOU)
XYZ
(PODEN-KOU)
R~7~D~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SRC~XYZ~ACR~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
FLH~ABRD~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(SRC-FLH)-XYZ-(ABRD-ACR)
(SRC-FLH)-XYZ-(ABRD-ACR)
G~1~2~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~
BNS~XYZ~YSW~RANDI~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CHG ~BORG~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(BNS-CHG)-XYZ-(BORG-YSW)-(-RANDI)
(BNS-CHG)-XYZ-(BORG-YSW)
(-RANDI)
K~S~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
DC ~XYZ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(DC)-XYZ
XYZ
(DC)
K~S~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CRX ~XYZ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CHG ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(CRX-CHG)-XYZ
XYZ
(CRX-CHG)
R~6~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
VSV ~XYZ~MC ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
POCH ~TAUNT~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(VSV-POCH)-XYZ-(TAUNT-MC)
(VSV-POCH)-XYZ
(TAUNT-MC)
K~S~1~J~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CWR~XYZ~LSR~MRP~ ~ ~ ~ ~ ~ ~ ~ ~ ~
CHOD~FLIN~PAIN~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(CWR-CHOD)-XYZ-(FLIN-LSR)-(PAIN-MRP)
XYZ-(FLIN-LSR)
(CWR-CHOD)
(PAIN-MRP)
K~S~1~2~ J~ ~ ~ ~ ~ ~ ~ ~ ~
CWR~XYZ~LSR~MRP~ HOG~ ~ ~ ~ ~ ~ ~ ~ ~
CHOD~FLIN~PAIN~ LMN ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
(CWR-CHOD)-XYZ-(FLIN-LSR)-(PAIN-MRP)-(LMN-HOG)
XYZ-(FLIN-LSR)-(PAIN-MRP)
(CWR-CHOD)
(LMN-HOG)
9 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 03:24:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
