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 Go to next message
yashi7777
Messages: 39
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
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:21
Please 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 #683504 is a reply to message #683491] Sun, 24 January 2021 18:14 Go to previous messageGo to next message
yashi7777
Messages: 39
Registered: March 2016
Member
Hi Michael,

was somehow my submission wrong? I mean do you need additional info. sorry i didnt get your reply. I submitted create table , inserts and also logic

[Updated on: Sun, 24 January 2021 18:36]

Report message to a moderator

Re: logic sql or plsql [message #683505 is a reply to message #683504] Mon, 25 January 2021 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
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 Go to previous messageGo to next message
yashi7777
Messages: 39
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 #683509 is a reply to message #683506] Mon, 25 January 2021 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
pls let me know if this is good.

Do you fit all the points I repeated?

For instance:

Also always post your Oracle version, with 4 decimals, as solution depends on it.

In these data, post several examples which cover all the cases you want to handle and can have.

An output is NOT a CREATE TABLE with INSERT statements but a chart.
Do you want your program generates CREATE TABLE and INSERT statements?
This is an output:
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Re: logic sql or plsql [message #683510 is a reply to message #683509] Mon, 25 January 2021 05:30 Go to previous messageGo to next message
yashi7777
Messages: 39
Registered: March 2016
Member
no the program doesnot require create table or inserts. if you create the table and inserts that will give you the output , which is the requirement
Re: logic sql or plsql [message #683511 is a reply to message #683510] Mon, 25 January 2021 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
. if you create the table and inserts that will give you the output

Post the output and "Explain each output column for each row."

This is quite simple.

[Updated on: Mon, 25 January 2021 05:57]

Report message to a moderator

Re: logic sql or plsql [message #683512 is a reply to message #683510] Mon, 25 January 2021 06:07 Go to previous messageGo to next message
yashi7777
Messages: 39
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 #683513 is a reply to message #683511] Mon, 25 January 2021 07:01 Go to previous messageGo to next message
yashi7777
Messages: 39
Registered: March 2016
Member
i have provided as you asked....please let me know if this helps...thanks again
Re: logic sql or plsql [message #683514 is a reply to message #683513] Mon, 25 January 2021 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Still not formatted and still no version.

Re: logic sql or plsql [message #683515 is a reply to message #683514] Mon, 25 January 2021 08:01 Go to previous messageGo to next message
yashi7777
Messages: 39
Registered: March 2016
Member
Sorry Michael, Oracle version 12c.


Not sure how to format. which part you need formatted can you please let me know.
Re: logic sql or plsql [message #683516 is a reply to message #683515] Mon, 25 January 2021 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
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:21
Please 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 Go to previous messageGo to next message
yashi7777
Messages: 39
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 #683520 is a reply to message #683517] Mon, 25 January 2021 10:48 Go to previous messageGo to next message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
Yashi, if you do not use [code] tags, as you have been asked several times, people may not wish to assist you.
Re: logic sql or plsql [message #683522 is a reply to message #683517] Mon, 25 January 2021 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

yashi7777 wrote on Mon, 25 January 2021 16:22
sorry 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 #683523 is a reply to message #683522] Mon, 25 January 2021 11:41 Go to previous messageGo to next message
yashi7777
Messages: 39
Registered: March 2016
Member
whenever i click i get this message

You have followed an invalid link. Please notify the webmaster how you got here.
Re: logic sql or plsql [message #683524 is a reply to message #683523] Mon, 25 January 2021 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, link has changed since the first time I posted it to you in 2016 but the one posted by BlackSwan in 2019 is valid.

Re: logic sql or plsql [message #683525 is a reply to message #683524] Mon, 25 January 2021 13:09 Go to previous messageGo to next message
yashi7777
Messages: 39
Registered: March 2016
Member
someone formatted it looks like. let me know if you need anything else.
Re: logic sql or plsql [message #683527 is a reply to message #683525] Mon, 25 January 2021 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
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 Go to previous messageGo to next message
yashi7777
Messages: 39
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 #683529 is a reply to message #683528] Mon, 25 January 2021 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 25 January 2021 20:46

...
And a formatted output.
And a correct version (with 4 decimals, not a commercial appellation).
Re: logic sql or plsql [message #683530 is a reply to message #683529] Mon, 25 January 2021 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And explain IN DETAILS "in below format": what, separator, order.

[Updated on: Mon, 25 January 2021 14:36]

Report message to a moderator

Re: logic sql or plsql [message #683531 is a reply to message #683530] Mon, 25 January 2021 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It could be something like that:
SQL> col field1 format a10
SQL> col field2 format a10
SQL> col field3 format a10
SQL> col field4 format a10
SQL> col field5 format a10
SQL> col field6 format a10
SQL> select field1,
...
 14  group by field1
 15  order by field1
 16  /
FIELD1     FIELD2     FIELD3     FIELD4     FIELD5     FIELD6
---------- ---------- ---------- ---------- ---------- ----------
1234567    X1 or X2   M,N,O,P,Q  No,Yes                100-101
7777777    X1         T1         Yes        111-222

2 rows selected.
But I'm not sure as your specifications are not complete and version is not known.

Re: logic sql or plsql [message #683532 is a reply to message #683530] Mon, 25 January 2021 14:43 Go to previous messageGo to next message
yashi7777
Messages: 39
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
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 #683534 is a reply to message #683533] Mon, 25 January 2021 15:08 Go to previous messageGo to next message
yashi7777
Messages: 39
Registered: March 2016
Member
Thanks Michael. Let me work on the sql that you provided with group by.....I think i understand now...I will get back...I appreciate your patience and grateful to the extent u helped me. Regards, Y
Re: logic sql or plsql [message #683535 is a reply to message #683534] Mon, 25 January 2021 15:36 Go to previous messageGo to next message
yashi7777
Messages: 39
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 Go to previous message
Michel Cadot
Messages: 67809
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','');
Previous Topic: print "+" when using to_char
Next Topic: How to Repeat Row by Specific Value in Data Field
Goto Forum:
  


Current Time: Sun Apr 11 13:21:34 CDT 2021