Home » SQL & PL/SQL » SQL & PL/SQL » Select Query suggestion (Oracle 12c)
Select Query suggestion [message #681490] Thu, 23 July 2020 08:06 Go to next message
akssre
Messages: 10
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 Statement :-
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);
I want a select statement with following conditions:-

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 #681491 is a reply to message #681490] Thu, 23 July 2020 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please, use code tags and align the columns in result.

Tell us what is the PK of your table.
And explain "between" in your sentence "It will only add one more in between". More specifically, "between" means there is an order which one it is.

In the end, feedback in your topics, we never had one in your previous one.

Re: Select Query suggestion [message #681492 is a reply to message #681491] Thu, 23 July 2020 13:13 Go to previous messageGo to next message
akssre
Messages: 10
Registered: March 2018
Junior Member
PK: Combination of all the column is PK.

S.no	NAME	TYPE    	TITLE	TOPSAL	BASESAL
1	B   	COMP    	T4   	4	8
2	B   	COMP    	T4   	10	12
3	B   	MEC     	T5   	12	15
4	C   	COMP    	T4   	3	4
5	C   	COMP-MID	T4   	4	8
6	C   	COMP    	T4   	8	12
7	C   	COMP-MID	T4   	12	16
8	C   	COMP    	T4   	16	20

Line number 5,7 are Between. Basically if i have two T4 records, There will be only 1 record in between, and it will be TOPSAL Asc. And if i have four T4 i should i have 3 records in between.

Apology for not providing the feedback earlier. Noted for the future.

Re: Select Query suggestion [message #681493 is a reply to message #681492] Thu, 23 July 2020 13:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How "between" is defined?
In your test case there is no serial number, if now there is one then post a new test case and tell us if this serial is the order key to define "between".

[Updated on: Thu, 23 July 2020 13:51]

Report message to a moderator

Re: Select Query suggestion [message #681495 is a reply to message #681493] Fri, 24 July 2020 01:28 Go to previous messageGo to next message
akssre
Messages: 10
Registered: March 2018
Junior Member
Serial number is just to show the numbering, it is not any column in the table. Kindly ignore it

Let me try once more to define the between condition:-

Lets say i have two records in my original table with the condition Title = 'T4' as below
Name	Type	Title	TopSal	BaseSal
C 	COMP	T4	4	8
C 	COMP	T4	10	12
Records are TOPSAL Ascending, now the result should have one more entry in between these two records,

Name, Title value should be same as the 1st record and TOPSAL will be the BASESAL of the 1st Record and BASESAL will be the TOP of actual 2nd Record, so output will be as follows :-
Name	Type	  Title	TopSal	BaseSal
C 	COMP	  T4	4	8
C 	COMP-MID  T4	8	10
C 	COMP	  T4	10	12
In similar way if we get 3 records in actual table as follows
Name	Type	Title	TopSal	BaseSal
C 	COMP	T4	4	8
C 	COMP	T4	10	12
C 	COMP	T4	16	20
The Output should be as follows
Name	Type	  Title	TopSal	BaseSal
C 	COMP	  T4	4	8
C 	COMP-MID  T4	8	10
C 	COMP	  T4	10	12
C 	COMP-MID  T4	12	16
C 	COMP	  T4	16	20
Re: Select Query suggestion [message #681496 is a reply to message #681495] Fri, 24 July 2020 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
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 Go to previous messageGo to next message
akssre
Messages: 10
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 #681509 is a reply to message #681504] Sat, 25 July 2020 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
order by NAME, topSAL, baseSAL
So you define an order on your rows.
This was my question.

But are you sure this is a valid order for your application?
Are you sure that topsal (for a name) is always increasing?

Re: Select Query suggestion [message #681513 is a reply to message #681509] Sat, 25 July 2020 07:25 Go to previous messageGo to next message
akssre
Messages: 10
Registered: March 2018
Junior Member
But are you sure this is a valid order for your application? :- Yes

Are you sure that topsal (for a name) is always increasing? :- Yes

Re: Select Query suggestion [message #681514 is a reply to message #681513] Sat, 25 July 2020 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
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
As you said nothing about this case, if there are only T5 the query returns nothing.
Re: Select Query suggestion [message #681515 is a reply to message #681514] Sat, 25 July 2020 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
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
Re: Select Query suggestion [message #681518 is a reply to message #681515] Sat, 25 July 2020 10:40 Go to previous message
akssre
Messages: 10
Registered: March 2018
Junior Member
Thank You.

Best Regards,
Previous Topic: Unable to retrieve a record containing NULL value
Next Topic: Query is taking 41 minute in Production environment
Goto Forum:
  


Current Time: Mon Sep 28 14:39:33 CDT 2020