Home » SQL & PL/SQL » SQL & PL/SQL » plsql logic
plsql logic [message #677088] Fri, 16 August 2019 15:03 Go to next message
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 #677089 is a reply to message #677088] Fri, 16 August 2019 15:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
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
Re: plsql logic [message #677090 is a reply to message #677089] Fri, 16 August 2019 15:26 Go to previous messageGo to next message
yashi7777
Messages: 42
Registered: March 2016
Member
I am sorry, but what am i missing ?
Re: plsql logic [message #677091 is a reply to message #677089] Fri, 16 August 2019 15:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 #677094 is a reply to message #677092] Sat, 17 August 2019 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

John, fix it with:
set sqlblanklines ON

Re: plsql logic [message #677095 is a reply to message #677094] Sat, 17 August 2019 01:19 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Ah, right.

btw, my favourite 19c new feature:

SET LINESIZE WINDOW
Re: plsql logic [message #677096 is a reply to message #677095] Sat, 17 August 2019 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Good to know, always had to count the characters. Smile

Re: plsql logic [message #677097 is a reply to message #677093] Sat, 17 August 2019 05:37 Go to previous messageGo to next message
yashi7777
Messages: 42
Registered: March 2016
Member
Thank you so much
Re: plsql logic [message #677100 is a reply to message #677097] Sat, 17 August 2019 10:23 Go to previous message
Michel Cadot
Messages: 68625
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

Previous Topic: Data spliting
Next Topic: xml generation from db
Goto Forum:
  


Current Time: Thu Mar 28 14:02:49 CDT 2024