Home » SQL & PL/SQL » SQL & PL/SQL » plsql logic
| plsql logic [message #677088] |
Fri, 16 August 2019 15:03  |
 |
yashi7777
Messages: 42
Registered: March 2016
|
Member |
|
|
I have a table like below with key and seq. My requirement is that the seq can start anywhere but for a given key if
there is atleast 5 consecutive sequences then i need to flag it as 'paid' else not 'nonpaid'. Can you help we with a plsql code please. I tried but couldn't figure out.
key seq
a 1
a 2
a 3
a 4
a 5
a 7
a 8
a 9
b 1
b 2
b 3
b 4
b 5
b 6
c 1
c 2
c 3
d 5
d 6
d 7
d 8
d 9
e 1
e 3
e 4
f 1
f 4
f 5
f 6
f 7
f 8
output
a=paid (since it has 1-5)
b=paid (since it has 1-5)
c=unpaid
d=paid (since it has 5-9)
e=unpaid
f=paid (since it has 4-8)
|
|
|
|
|
|
|
|
| Re: plsql logic [message #677091 is a reply to message #677089] |
Fri, 16 August 2019 15:36  |
|
yashi7777
Messages: 42
Registered: March 2016
|
Member |
|
|
I think this is what you wanted the ddl and insert and also i am in 12c
CREATE TABLE MOCHA
(
NAME_X VARCHAR2(1 BYTE),
SEQ_I NUMBER
);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('a', 1);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('a', 2);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('a', 3);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('a', 4);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('a', 5);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('a', 7);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('a', 8);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('a', 9);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('b', 1);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('b', 2);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('b', 3);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('b', 4);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('b', 5);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('b', 6);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('c', 1);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('c', 2);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('c', 3);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('d', 5);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('d', 6);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('d', 7);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('d', 8);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('d', 9);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('e', 1);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('e', 3);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('e', 4);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('f', 1);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('f', 4);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('f', 5);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('f', 6);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('f', 7);
Insert into MOCHA
(NAME_X, SEQ_I)
Values
('f', 8);
COMMIT;
--moderator edit: corrected closing [/code] tag
[Updated on: Sat, 17 August 2019 01:02] by Moderator Report message to a moderator |
|
|
|
| Re: plsql logic [message #677092 is a reply to message #677091] |
Sat, 17 August 2019 01:03 |
John Watson
Messages: 8803
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your code doesn't work:orclz>
orclz> CREATE TABLE MOCHA
2
orclz> (
2
orclz> NAME_X VARCHAR2(1 BYTE),
SP2-0734: unknown command beginning "NAME_X VA..." - rest of line ignored.
orclz>
orclz> SEQ_I NUMBER
SP2-0734: unknown command beginning "SEQ_I NU..." - rest of line ignored.
orclz>
orclz> );
SP2-0042: unknown command ")" - rest of line ignored.
orclz> |
|
|
|
| Re: plsql logic [message #677093 is a reply to message #677091] |
Sat, 17 August 2019 01:16 |
 |
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Also always post your Oracle version, with 4 decimals, as solution depends on it.
One way:
SQL> with
2 data as (
3 select NAME_X, SEQ_I, lag(SEQ_I,1,-1) over (partition by NAME_X order by SEQ_I) prev_seq
4 from MOCHA
5 ),
6 counting (NAME_X, SEQ_I, cnt) as (
7 select NAME_X, SEQ_I, 1
8 from data
9 where prev_seq != SEQ_I-1
10 union all
11 select d.NAME_X, d.SEQ_I, c.cnt+1
12 from counting c, data d
13 where d.NAME_X = c.NAME_X and d.SEQ_I = c.SEQ_I+1
14 )
15 select NAME_X,
16 case when max(cnt) >= 5 then 'paid' else 'unpaid' end flag
17 from counting
18 group by NAME_X
19 order by NAME_X
20 /
N FLAG
- ------
a paid
b paid
c unpaid
d paid
e unpaid
f paid
6 rows selected.
[Edit: slight improvement in query]
[Updated on: Wed, 21 August 2019 01:39] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: plsql logic [message #677100 is a reply to message #677097] |
Sat, 17 August 2019 10:23 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Just for fun, adding the following rows:
insert into MOCHA values ('f',10);
insert into MOCHA values ('f',11);
insert into MOCHA values ('f',20);
insert into MOCHA values ('f',21);
insert into MOCHA values ('f',22);
insert into MOCHA values ('f',23);
insert into MOCHA values ('f',24);
insert into MOCHA values ('f',25);
commit;SQL> col reason format a50
SQL> with
2 data as (
3 select NAME_X, SEQ_I, lag(SEQ_I,1,-1) over (partition by NAME_X order by SEQ_I) prev_seq
4 from MOCHA
5 ),
6 counting (NAME_X, SEQ_I, min_seq, cnt) as (
7 select NAME_X, SEQ_I, SEQ_I, 1
8 from data
9 where prev_seq != SEQ_I-1
10 union all
11 select d.NAME_X, d.SEQ_I, c.min_seq, c.cnt+1
12 from counting c, data d
13 where d.NAME_X = c.NAME_X and d.SEQ_I = c.SEQ_I+1
14 ),
15 grouping as (
16 select NAME_X, min_seq, max(SEQ_I) max_seq, max(cnt) cnt
17 from counting
18 group by NAME_X, min_seq
19 )
20 select NAME_X,
21 case when max(cnt) >= 5 then 'paid' else 'unpaid' end flag,
22 listagg(case when cnt >= 5 then min_seq||'->'||max_seq end,', ')
23 within group (order by min_seq)
24 reason
25 from grouping
26 group by NAME_X
27 order by NAME_X
28 /
N FLAG REASON
- ------ --------------------------------------------------
a paid 1->5
b paid 1->6
c unpaid
d paid 5->9
e unpaid
f paid 4->8, 20->25
6 rows selected.
[Edit: slight improvement in query]
[Updated on: Wed, 21 August 2019 01:42] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed Mar 22 07:13:06 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
