FLAG BASED ON PREVIOUS ROWS [message #613731] |
Fri, 09 May 2014 14:06 |
|
nsk78
Messages: 6 Registered: May 2014
|
Junior Member |
|
|
MY INPUT
IS THE FIRST 3 COLUMNS.i NEED THE OUT PUT AS IN I FLAG.IF THE DRUG A+B FOLLoWED BY A ONLY THEN ITS MAIN PHASE.all subsequent a only becomes MAIN AS WELL.
HOW SHOULD i DO THIS ?i USED CURSOR TO DO THIS.pLEASE HELP ME TO DO THIS with query.
ID DRUG ORDER FLAG
1 A+B 1 NOT_MAIN
1 A ONLY 2 MAIN
1 A ONLY 3 MAIN
1 B 4 NOT_MAIN
1 A ONLY 5 NOT_MAIN
1 A ONLY 6 NOT_MAIN
1 A+B 7 NOT_MAIN
1 A ONLY 8 MAIN
1 A_ONLY 9 MAIN
1 A ONLY 10 MAIN
[Updated on: Fri, 09 May 2014 14:09] Report message to a moderator
|
|
|
|
Re: FLAG BASED ON PREVIOUS ROWS [message #613735 is a reply to message #613734] |
Fri, 09 May 2014 14:29 |
|
nsk78
Messages: 6 Registered: May 2014
|
Junior Member |
|
|
DATA is to be ordered based on id and order.
Followed,
if id 1 has A only and his immediate fill above is a+b then he is main,
or if id 1 drug is a only and if the previous flag is set to main then its main.
VERSION 10 G.
CREATE TABLE(ID NUMBER,DRUG VARCHAR2(10),DRUG_ORDER NUMBER);
INSERT INTO TEST VALUES(1,'A+B',1); --NOT MAIN
INSERT INTO TEST VALUES(1,'A ONLY',2);--MAIN
INSERT INTO TEST VALUES(1,'A ONLY',3);--MAIN
INSERT INTO TEST VALUES(1,'C',4);--NOT MAIN
INSERT INTO TEST VALUES(1,'A ONLY',5);--NOT MAIN
INSERT INTO TEST VALUES(1,'A ONLY',6);--NOT
INSERT INTO TEST VALUES(1,'A+B',7);--MAIN
INSERT INTO TEST VALUES(1,'A ONLY',8);--MAIN
INSERT INTO TEST VALUES(1,'A ONLY',9);--MAIN
|
|
|
Re: FLAG BASED ON PREVIOUS ROWS [message #613736 is a reply to message #613735] |
Fri, 09 May 2014 14:38 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
SQL> select *
2 from test
3 order by drug_order
4 /
ID DRUG DRUG_ORDER
---------- ---------- ----------
1 A+B 1
1 A ONLY 2
1 A ONLY 3
1 C 4
1 A ONLY 5
1 A ONLY 6
1 A+B 7
1 A ONLY 8
1 A ONLY 9
9 rows selected.
SQL> select id,
2 drug,
3 drug_order,
4 case
5 when drug = 'A ONLY'
6 and
7 last_value(
8 case drug
9 when 'A ONLY' then null
10 else drug
11 end ignore nulls
12 )
13 over(partition by id order by drug_order) = 'A+B' then 'MAIN'
14 else 'NOT_MAIN'
15 end flag
16 from test
17 order by drug_order
18 /
ID DRUG DRUG_ORDER FLAG
---------- ---------- ---------- --------
1 A+B 1 NOT_MAIN
1 A ONLY 2 MAIN
1 A ONLY 3 MAIN
1 C 4 NOT_MAIN
1 A ONLY 5 NOT_MAIN
1 A ONLY 6 NOT_MAIN
1 A+B 7 NOT_MAIN
1 A ONLY 8 MAIN
1 A ONLY 9 MAIN
9 rows selected.
SQL>
SY.
|
|
|
|
|
|
|
Re: FLAG BASED ON PREVIOUS ROWS [message #613745 is a reply to message #613740] |
Fri, 09 May 2014 22:46 |
|
nsk78
Messages: 6 Registered: May 2014
|
Junior Member |
|
|
drug order should increment for not main and remain the same for main
id drug drug_order flag drug_order_chg
1 A+B 1 NOT_MAIN 1
1 A ONLY 2 MAIN 1
1 A ONLY 3 MAIN 1
1 C 4 NOT_MAIN 2
1 A ONLY 5 NOT_MAIN 3
1 A ONLY 6 NOT_MAIN 4
1 A+B 7 NOT_MAIN 5
1 A ONLY 8 MAIN 5
1 A ONLY 9 MAIN 5
[Updated on: Fri, 09 May 2014 22:48] Report message to a moderator
|
|
|
|