Home » SQL & PL/SQL » SQL & PL/SQL » Select Query suggestion (Oracle 12c)
| Select Query suggestion [message #681490] |
Thu, 23 July 2020 08:06  |
 |
akssre
Messages: 26
Registered: March 2018
|
Junior Member |
|
|
Hello Experts,
I need your valuable suggestion on the below problem.
create table RESULT
(
NAME VARCHAR2(10),
TYPE VARCHAR2(10),
TITLE VARCHAR2(10),
TOPSAL NUMBER,
BASESAL NUMBER
)
INSERT INTO RESULT VALUES( 'A','MEC','T1',100,110);
INSERT INTO RESULT VALUES('A','ELEC','T2',110,120);
INSERT INTO RESULT VALUES('A','CHEM','T3',120,130);
INSERT INTO RESULT VALUES('B','MEC','T1',1,4);
INSERT INTO RESULT VALUES('B','COMP','T4',4,8);
INSERT INTO RESULT VALUES('B','COMP-1','T4',10,12);
INSERT INTO RESULT VALUES('B','MEC','T5',12,15);
INSERT INTO RESULT VALUES('C','MEC','T1',1,2);
INSERT INTO RESULT VALUES('C','ELEC','T2',2,3);
INSERT INTO RESULT VALUES('C','COMP','T4',3,4);
INSERT INTO RESULT VALUES('C','COMP-1','T4',8,12);
INSERT INTO RESULT VALUES('C','COMP-2','T4',16,20);
1. Select only record where TITLE IN ('T4,'T5'), IF NAME has both( 'T4','T5'), select all record as it is.
2. If Title = 'T4' ONLY for any name,
Then while selecting the records, add COMP-MID in between other TYPE(COMP),and value of TOPSAL will be BASESAL of previous record and BASESAL will be the TOPSAL of the next record.
so If originally i have only two record having Title = 'T4', It will only add one more in between, while if i have three records, it will have two new records in between while selecting the record as per the above mentioned rule.
NAME TYPE TITLE TOPSAL BASESAL
B COMP T4 4 8
B COMP T4 10 12
B MEC T5 12 15
C COMP T4 3 4
C COMP-MID T4 4 8
C COMP T4 8 12
C COMP-MID T4 12 16
C COMP T4 16 20
Best Regards,
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Select Query suggestion [message #681496 is a reply to message #681495] |
Fri, 24 July 2020 01:36  |
 |
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
"1st" and "2nd" are meaningless unless you define an order from the data.
"previous" and "next" as well as "between" are defined only an order is defined in the row set.
Remember a table is a heap (a set) of rows.
[Updated on: Fri, 24 July 2020 01:37] Report message to a moderator |
|
|
|
| Re: Select Query suggestion [message #681504 is a reply to message #681496] |
Sat, 25 July 2020 04:41 |
|
akssre
Messages: 26
Registered: March 2018
|
Junior Member |
|
|
Michel,
I am not able to describe they way you want to, for better understanding of all.
I came up with following query after digging here and there. Which is doing what I am looking for, I am not sure what will be the behavior or result with million records. May be it fail for some, I am not sure.
If you can have a look and suggest some other way, it will be great.
SELECT *
FROM (select NAME, TYPE, TITLE, TOPSAL, BASESAL
from RESULT
where NAME in (select distinct NAME from RESULT where TITLE = 'T5')
and TITLE IN ('T4', 'T5')
UNION
select NAME, TYPE, TITLE, TOPSAL, BASESAL
from (select NAME, TYPE, TITLE, TOPSAL, BASESAL
from RESULT
WHERE NAME not in
(select distinct NAME from RESULT where TITLE = 'T5')
and TITLE = 'T4'
UNION
select a.NAME,
a.TYPE || '-MID' TYPE,
a.TITLE,
A.PREV_BASE,
A.NEXT_TOP
from (select NAME,
lag(TYPE) over(partition by NAME Order by NAME) TYPE,
TITLE,
topSAL,
baseSAL,
lag(baseSAL) over(partition by NAME Order by NAME) prev_base,
topSAL next_top
from RESULT
where NAME not in (select distinct NAME
from RESULT
where TITLE = 'T5')
and TITLE = 'T4'
order by NAME, topSAL, baseSAL) a
where prev_base is not null
order by 1, 4, 5, 2) A) B
order by 1, 4, 5, 2
|
|
|
|
|
|
|
|
| Re: Select Query suggestion [message #681514 is a reply to message #681513] |
Sat, 25 July 2020 08:50 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 data as (
3 select NAME, TYPE, TITLE, TOPSAL, BASESAL,
4 count(distinct TITLE) over (partition by NAME) nbType,
5 count(TITLE) over (partition by NAME) nbT4,
6 row_number() over (partition by NAME order by topSAL, baseSAL) rn
7 from RESULT
8 where TITLE in ('T4','T5')
9 )
10 select NAME, TYPE, TITLE, TOPSAL, BASESAL
11 from data
12 where nbType = 2
13 union all
14 select NAME, TYPE, TITLE, TOPSAL, BASESAL
15 from data
16 where nbType = 1 and TITLE = 'T4'
17 union all
18 select NAME, TYPE, TITLE, TOPSAL, BASESAL
19 from ( select NAME, 'COMP-MID' TYPE, TITLE,
20 BASESAL TOPSAL,
21 lead (TOPSAL) over (partition by NAME order by TOPSAL, BASESAL) BASESAL,
22 rn, nbT4
23 from data
24 where nbType = 1 and TITLE = 'T4'
25 )
26 where rn < nbT4
27 order by NAME, TOPSAL, BASESAL
28 /
NAME TYPE TITLE TOPSAL BASESAL
---------- ---------- ---------- ---------- ----------
B COMP T4 4 8
B COMP-1 T4 10 12
B MEC T5 12 15
C COMP T4 3 4
C COMP-MID T4 4 8
C COMP-1 T4 8 12
C COMP-MID T4 12 16
C COMP-2 T4 16 20
|
|
|
|
| Re: Select Query suggestion [message #681515 is a reply to message #681514] |
Sat, 25 July 2020 09:15 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You can factorize the T4 part and decrease the work:
SQL> with
2 data as (
3 select NAME, TYPE, TITLE, TOPSAL, BASESAL,
4 count(distinct TITLE) over (partition by NAME) nbType
5 from RESULT
6 where TITLE in ('T4','T5')
7 ),
8 data2 as (
9 select NAME, TYPE, TITLE, TOPSAL, BASESAL,
10 count(TITLE) over (partition by NAME) nbT4,
11 row_number() over (partition by NAME order by topSAL, baseSAL) rn
12 from data
13 where nbType = 1 and TITLE = 'T4'
14 )
15 select NAME, TYPE, TITLE, TOPSAL, BASESAL
16 from data
17 where nbType = 2
18 union all
19 select NAME, TYPE, TITLE, TOPSAL, BASESAL
20 from data2
21 union all
22 select NAME, TYPE, TITLE, TOPSAL, BASESAL
23 from ( select NAME, 'COMP-MID' TYPE, TITLE,
24 BASESAL TOPSAL,
25 lead (TOPSAL) over (partition by NAME order by topSAL, baseSAL) BASESAL,
26 rn, nbT4
27 from data2
28 )
29 where rn < nbT4
30 order by NAME, topSAL, baseSAL
31 /
NAME TYPE TITLE TOPSAL BASESAL
---------- ---------- ---------- ---------- ----------
B COMP T4 4 8
B COMP-1 T4 10 12
B MEC T5 12 15
C COMP T4 3 4
C COMP-MID T4 4 8
C COMP-1 T4 8 12
C COMP-MID T4 12 16
C COMP-2 T4 16 20 |
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 23 22:01:11 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
