Home » SQL & PL/SQL » SQL & PL/SQL » logic sql or plsql (merged)
logic sql or plsql (merged) [message #683487] |
Sat, 23 January 2021 13:09 |
|
yashi7777
Messages: 42 Registered: March 2016
|
Member |
|
|
Gurus; how to implement the code logic. is it possible via single sql or plsql....can you help on the sql or plsql writing
This is the source data and what output is expected
create table source
(Field1 varchar2(100),
Field2 varchar2(100),
Field3 varchar2(100),
Field4 varchar2(100),
Field5 varchar2(100),
Field6 varchar2(100));
insert into source values ('1234567','X','R','Req1','C','');
insert into source values ('1234567','X','Q','Req1','D','');
insert into source values ('1234567','Y','R','M',,);
insert into source values ('1234567','Y','R','N',,);
insert into source values ('1234567','Y','R','O',,);
insert into source values ('1234567','Y','R','P',,);
insert into source values ('1234567','Y','R','Q',,);
insert into source values ('1234567','Z','R','X1',,);
insert into source values ('1234567','Z','R','X2',,);
insert into source values ('1234567','W','R','','100','101');
insert into source values ('7777777','X','R','Req1','C','');
insert into source values ('7777777','Z','R','X1',,);
insert into source values ('7777777','E','R','T1',,);
insert into source values ('7777777','F','R','T2','111','222');
commit;
create table output
(Field1 varchar2(100),
Field2 varchar2(100),
Field3 varchar2(100),
Field4 varchar2(100),
Field5 varchar2(100),
Field6 varchar2(100));
insert into output values ('1234567','X1 or X2','M,N,O,P,Q','Yes,No','','100-101');
insert into output values ('7777777','X1','T1','Yes','111-222','');
commit;
I have provided the data and logic in the attached document. Thanks for your help.
|
|
|
Re: logic sql or plsql [message #683491 is a reply to message #683487] |
Sat, 23 January 2021 14:13 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Thu, 31 March 2016 10:05
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Your requirements are not clear.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
In these data, post several examples which cover all the cases you want to handle and can have.
Michel Cadot wrote on Thu, 31 March 2016 19:56...
Explain each output column for each row.
...
BlackSwan wrote on Fri, 16 August 2019 22:21Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Michel Cadot wrote on Sat, 17 August 2019 08:16
Also always post your Oracle version, with 4 decimals, as solution depends on it.
...
[Updated on: Sat, 23 January 2021 14:14] Report message to a moderator
|
|
|
|
Re: logic sql or plsql [message #683505 is a reply to message #683504] |
Mon, 25 January 2021 00:01 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Some of us can't and don't want to download stuff from the web.
What can be posted in text in the post should be posted in text in the post.
So what is wrong?
As already said:
- no format
- no version
- no output
- no rules to come from the data to the output.
And avoid all these useless blank lines, you already saw in your previous topics this is painful.
[Updated on: Mon, 25 January 2021 00:06] Report message to a moderator
|
|
|
Re: logic sql or plsql [message #683506 is a reply to message #683505] |
Mon, 25 January 2021 04:14 |
|
yashi7777
Messages: 42 Registered: March 2016
|
Member |
|
|
Michael, I have added the logic for each field below , so the attached is not needed...pls let me know if this is good. Thanks for your help.
create table source
(Field1 varchar2(100),
Field2 varchar2(100),
Field3 varchar2(100),
Field4 varchar2(100),
Field5 varchar2(100),
Field6 varchar2(100));
insert into source values ('1234567','X','R','Req1','C','');
insert into source values ('1234567','X','Q','Req1','D','');
insert into source values ('1234567','Y','R','M',,);
insert into source values ('1234567','Y','R','N',,);
insert into source values ('1234567','Y','R','O',,);
insert into source values ('1234567','Y','R','P',,);
insert into source values ('1234567','Y','R','Q',,);
insert into source values ('1234567','Z','R','X1',,);
insert into source values ('1234567','Z','R','X2',,);
insert into source values ('1234567','W','R','','100','101');
insert into source values ('7777777','X','R','Req1','C','');
insert into source values ('7777777','Z','R','X1',,);
insert into source values ('7777777','E','R','T1',,);
insert into source values ('7777777','F','R','T2','111','222');
commit;
create table output
(Field1 varchar2(100),-- Field 1 from source table
Field2 varchar2(100),--If Field2=Z in source table then get values of Field 4
Field3 varchar2(100),--If Field2=Y or E in source table then get values of Field 4
Field4 varchar2(100),--If Field2=X and Field4=Req1 and Field3=R in source table then get values of Field5 and name it C=Yes else if Field3=Q then D=No
Field5 varchar2(100),--If Field2=F in source table then get values of Field5 and Field6
Field6 varchar2(100)--If Field2=W in source table then get values of Field5 and Field6
);
insert into output values ('1234567','X1 or X2','M,N,O,P,Q','Yes,No','','100-101');
insert into output values ('7777777','X1','T1','Yes','111-222','');
commit;
[Updated on: Mon, 25 January 2021 05:16] Report message to a moderator
|
|
|
|
|
|
Re: logic sql or plsql [message #683512 is a reply to message #683510] |
Mon, 25 January 2021 06:07 |
|
yashi7777
Messages: 42 Registered: March 2016
|
Member |
|
|
create table source
(Field1 varchar2(100),
Field2 varchar2(100),
Field3 varchar2(100),
Field4 varchar2(100),
Field5 varchar2(100),
Field6 varchar2(100));
insert into source values ('1234567','X','R','Req1','C','');
insert into source values ('1234567','X','Q','Req1','D','');
insert into source values ('1234567','Y','R','M',,);
insert into source values ('1234567','Y','R','N',,);
insert into source values ('1234567','Y','R','O',,);
insert into source values ('1234567','Y','R','P',,);
insert into source values ('1234567','Y','R','Q',,);
insert into source values ('1234567','Z','R','X1',,);
insert into source values ('1234567','Z','R','X2',,);
insert into source values ('1234567','W','R','','100','101');
insert into source values ('7777777','X','R','Req1','C','');
insert into source values ('7777777','Z','R','X1',,);
insert into source values ('7777777','E','R','T1',,);
insert into source values ('7777777','F','R','T2','111','222');
commit;
Target table
----------------------------------------------------------------------------------------------
Output Field1 | Output field2 |Output field3| Output field4| Output field5 | Output field6|
1234567 | X1 or X2 |M,N,O,P,Q | Yes,No | | 100-101 |
7777777 | X1 |T1 | Yes | 111-222 |
----------------------------------------------------------------------------------------------
Logic how to get output from source
Ouput Field1 --Source key field
Ouput Field2 --If Field2=Z in source table then get values of Field 4 from source table in below format
Ouput Field3 --If Field2=Y or E in source table then get values of Field 4 from source table in below format
Ouput Field4 --If Field2=X and Field4=Req1 and Field3=R then get values of Field5 and name it C=Yes else if Field3=Q then D=No from source table in below format
Ouput Filed5 --If Field2=F in source table then get values of Field5 and Field6 from source table in below format
Ouput Field6 --If Field2=W in source table then get values of Field5 and Field6 from source table in below format
[Updated on: Mon, 25 January 2021 07:00] Report message to a moderator
|
|
|
|
|
|
Re: logic sql or plsql [message #683516 is a reply to message #683515] |
Mon, 25 January 2021 09:02 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Sat, 23 January 2021 21:13
Michel Cadot wrote on Thu, 31 March 2016 10:05
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Your requirements are not clear.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
In these data, post several examples which cover all the cases you want to handle and can have.
Michel Cadot wrote on Thu, 31 March 2016 19:56...
Explain each output column for each row.
...
BlackSwan wrote on Fri, 16 August 2019 22:21Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Michel Cadot wrote on Sat, 17 August 2019 08:16
Also always post your Oracle version, with 4 decimals, as solution depends on it.
...
Query v$version.
[Updated on: Mon, 25 January 2021 09:03] Report message to a moderator
|
|
|
Re: logic sql or plsql [message #683517 is a reply to message #683516] |
Mon, 25 January 2021 09:22 |
|
yashi7777
Messages: 42 Registered: March 2016
|
Member |
|
|
sorry not sure wnat am i missing;
can you just help me with one column output and i can work on the rest
create table source
(Field1 varchar2(100),
Field2 varchar2(100),
Field3 varchar2(100),
Field4 varchar2(100),
Field5 varchar2(100),
Field6 varchar2(100));
insert into source values ('1234567','X','R','Req1','C','');
insert into source values ('1234567','X','Q','Req1','D','');
insert into source values ('1234567','Y','R','M',,);
insert into source values ('1234567','Y','R','N',,);
insert into source values ('1234567','Y','R','O',,);
insert into source values ('1234567','Y','R','P',,);
insert into source values ('1234567','Y','R','Q',,);
insert into source values ('1234567','Z','R','X1',,);
insert into source values ('1234567','Z','R','X2',,);
insert into source values ('1234567','W','R','','100','101');
insert into source values ('7777777','X','R','Req1','C','');
insert into source values ('7777777','Z','R','X1',,);
insert into source values ('7777777','E','R','T1',,);
insert into source values ('7777777','F','R','T2','111','222');
commit;
logic to get the output
Output Field1 --Source key field ( output expected 1234567)
Output Field2 --If Field2 from source table=Z then get values of Field 4 from source table in below format (output expected X1 or X2)
Output table will look like this
--------------------------------
output field1 | output field 2|
-----------------------------------
1234567 | X1 or X2 |
--moderator edit: added [code] tags, please do so yourself in future.
[Updated on: Mon, 25 January 2021 10:49] by Moderator Report message to a moderator
|
|
|
|
Re: logic sql or plsql [message #683522 is a reply to message #683517] |
Mon, 25 January 2021 11:28 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
yashi7777 wrote on Mon, 25 January 2021 16:22sorry not sure wnat am i missing;
Can you click on the link we posted many times and read it?
Also read your previous topics to see how people did to help you.
[Updated on: Mon, 25 January 2021 11:28] Report message to a moderator
|
|
|
|
|
|
Re: logic sql or plsql [message #683527 is a reply to message #683525] |
Mon, 25 January 2021 13:46 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Yes, a VALID test case:
SQL> insert into source values ('1234567','X','R','Req1','C','');
1 row created.
SQL> insert into source values ('1234567','X','Q','Req1','D','');
1 row created.
SQL> insert into source values ('1234567','Y','R','M',,);
insert into source values ('1234567','Y','R','M',,)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into source values ('1234567','Y','R','N',,);
insert into source values ('1234567','Y','R','N',,)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into source values ('1234567','Y','R','O',,);
insert into source values ('1234567','Y','R','O',,)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into source values ('1234567','Y','R','P',,);
insert into source values ('1234567','Y','R','P',,)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into source values ('1234567','Y','R','Q',,);
insert into source values ('1234567','Y','R','Q',,)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into source values ('1234567','Z','R','X1',,);
insert into source values ('1234567','Z','R','X1',,)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into source values ('1234567','Z','R','X2',,);
insert into source values ('1234567','Z','R','X2',,)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into source values ('1234567','W','R','','100','101');
1 row created.
SQL> insert into source values ('7777777','X','R','Req1','C','');
1 row created.
SQL> insert into source values ('7777777','Z','R','X1',,);
insert into source values ('7777777','Z','R','X1',,)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into source values ('7777777','E','R','T1',,);
insert into source values ('7777777','E','R','T1',,)
*
ERROR at line 1:
ORA-00936: missing expression
And a formatted output.
And a correct version (with 4 decimals, not a commercial appellation).
|
|
|
Re: logic sql or plsql [message #683528 is a reply to message #683527] |
Mon, 25 January 2021 14:02 |
|
yashi7777
Messages: 42 Registered: March 2016
|
Member |
|
|
insert into source values ('1234567','X','R','Req1','C','');
insert into source values ('1234567','X','Q','Req1','D','');
insert into source values ('1234567','Y','R','M','','');
insert into source values ('1234567','Y','R','N','','');
insert into source values ('1234567','Y','R','O','','');
insert into source values ('1234567','Y','R','P','','');
insert into source values ('1234567','Y','R','Q','','');
insert into source values ('1234567','Z','R','X1','','');
insert into source values ('1234567','Z','R','X2','','');
insert into source values ('1234567','W','R','','100','101');
insert into source values ('7777777','X','R','Req1','C','');
insert into source values ('7777777','Z','R','X1','','');
insert into source values ('7777777','E','R','T1','','');
insert into source values ('7777777','F','R','T2','111','222');
commit;
sorry, try this please
|
|
|
|
|
|
Re: logic sql or plsql [message #683532 is a reply to message #683530] |
Mon, 25 January 2021 14:43 |
|
yashi7777
Messages: 42 Registered: March 2016
|
Member |
|
|
output
create table output
(Field1 varchar2(100),
Field2 varchar2(100),--If Field2=Z then get values of Field 4
Field3 varchar2(100),--If Field2=Y or E then get values of Field 4
Field4 varchar2(100),--If Field2=X and Field4=Req1 and Field3=R then get values of Field5 and name it C=Yes else if Field3=Q then D=No
Field5 varchar2(100),--If Field2=F then get values of Field5 and Field6
Field6 varchar2(100)--If Field2=W then get values of Field5 and Field6
);
insert into output values ('1234567','X1 or X2','M,N,O,P,Q','Yes,No','','100-101');
insert into output values ('7777777','X1','T1','Yes','111-222','');
commit;
version oracle 12c
|
|
|
Re: logic sql or plsql [message #683533 is a reply to message #683532] |
Mon, 25 January 2021 14:53 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Why 'X1 or X2' and not 'X2 or X1'?
Why 'M,N,O,P,Q' and not 'Q,N,O,P,M'?
Why 'Yes,No' and not 'No,Yes'?
What is the result if I add those rows:
insert into source values ('1234567','W','R','','102','103');
insert into source values ('7777777','F','R','T2','112','113');
Post the result as a chart like:
FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 FIELD6
---------- ---------- ---------- ---------- ---------- ----------
1234567 X2 or X1 Q,N,O,P,M Yes,No 100-101
7777777 X1 T1 Yes 111-222
Quote:version oracle 12c
This is NOT what I asked, many times.
[Updated on: Mon, 25 January 2021 14:53] Report message to a moderator
|
|
|
|
Re: logic sql or plsql [message #683535 is a reply to message #683534] |
Mon, 25 January 2021 15:36 |
|
yashi7777
Messages: 42 Registered: March 2016
|
Member |
|
|
Michael,
Sorry I didn't read your email properly.
SQL> select field1,
...
14 group by field1
15 order by field1
16 /
what do i write to get the output columns....'X1 or X2'/'M,N,O,P,Q'/ 'Yes,No'....the order of the values doesnot matter to answer your question....why not 'X2 or X1' ,etc
[Updated on: Mon, 25 January 2021 15:55] Report message to a moderator
|
|
|
Re: logic sql or plsql [message #683536 is a reply to message #683535] |
Mon, 25 January 2021 23:59 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
And what about my question if I add the 2 rows?
And if I add these 2 more (so 4 rows in addition to your test case):
insert into source values ('1234567','W','R','','','105');
insert into source values ('7777777','F','R','T2','115','');
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:30:04 CDT 2024
|