Home » SQL & PL/SQL » SQL & PL/SQL » FLAG BASED ON PREVIOUS ROWS
FLAG BASED ON PREVIOUS ROWS [message #613731] Fri, 09 May 2014 14:06 Go to next message
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 #613734 is a reply to message #613731] Fri, 09 May 2014 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Define "FOLLOWED".
Followed means there is an order, how do you define the order from the data (which is the only thing SQL knows)?
Please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Before, Please How to use [code] tags and make your code easier to read.
Always post your Oracle version, solutions depend on it.



Re: FLAG BASED ON PREVIOUS ROWS [message #613735 is a reply to message #613734] Fri, 09 May 2014 14:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #613737 is a reply to message #613735] Fri, 09 May 2014 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In your first post you said: "IF THE DRUG A+B FOLLoWED BY A ONLY THEN ITS MAIN", so why first row is not MAIN like 7 one?

Re: FLAG BASED ON PREVIOUS ROWS [message #613738 is a reply to message #613736] Fri, 09 May 2014 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@SY

Line 7 is MAIN in example.
There is an inconsistency between lines 1 and 7 in OP's example (and first post).

Re: FLAG BASED ON PREVIOUS ROWS [message #613739 is a reply to message #613736] Fri, 09 May 2014 15:02 Go to previous messageGo to next message
nsk78
Messages: 6
Registered: May 2014
Junior Member
Thank You.I misunderstood the last value function.I thought it gives the last value in the sET.I have a long way to go.Thank you so much.
Re: FLAG BASED ON PREVIOUS ROWS [message #613740 is a reply to message #613739] Fri, 09 May 2014 15:35 Go to previous messageGo to next message
nsk78
Messages: 6
Registered: May 2014
Junior Member
line 7 is not main..sorry for the typo..
Re: FLAG BASED ON PREVIOUS ROWS [message #613745 is a reply to message #613740] Fri, 09 May 2014 22:46 Go to previous messageGo to next message
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

Re: FLAG BASED ON PREVIOUS ROWS [message #613746 is a reply to message #613745] Fri, 09 May 2014 23:09 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Previous Topic: How to downgrade Oracle version in a session
Next Topic: procedure to read file from ftp login and write it into another table.
Goto Forum:
  


Current Time: Fri Mar 29 02:58:57 CDT 2024