Home » SQL & PL/SQL » SQL & PL/SQL » How to acheive this logic in single procedure? (merged)
How to acheive this logic in single procedure? (merged) [message #392439] Wed, 18 March 2009 00:26 Go to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
SQL> Create table TEST_NEW (STATS                          VARCHAR2(50) ,
  2  CODE                           VARCHAR2(5) ,
  3  LOC                            VARCHAR2(2) ,
  4  KLI                            VARCHAR2(5) ,
  5  TYPE                           VARCHAR2(2) ,
  6  PROPTION                       VARCHAR2(50) ,
  7  ORDER_NO                       VARCHAR2(50) ,
  8  PREPRO                         VARCHAR2(50) );

Table created.

SQL> Insert into TEST_NEW values('W', 'O', 'AA', '68', 'E', 'P1', '1', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'BB', '66', 'E', 'P1', '2', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'BB', '67', 'E', 'P1', '3', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'CC', '22', 'E', 'P1', '4', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'DD', '69', 'E', 'P1', '5', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'DD', '72', 'E', 'P1', '6', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'DD', 'ALL', 'L', 'P1', '4', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'AA', 'ALL', 'L', 'P1', '1', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'BB', 'ALL', 'L', 'P1', '2', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'CC', 'ALL', 'L', 'P1', '3', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'EE', '28', 'E', 'P2', '7', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'FF', '27', 'E', 'P2', '8', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'GG', '20', 'E', 'P2', '9', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'GG', '21', 'E', 'P2', '10', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'GG', '32', 'E', 'P2', '11', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'HH', '60', 'E', 'P2', '12', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'HH', '61', 'E', 'P2', '13', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'HH', '64', 'E', 'P2', '14', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'EE', 'ALL', 'L', 'P2', '5', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'FF', 'ALL', 'L', 'P2', '6', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'GG', 'ALL', 'L', 'P2', '7', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'HH', 'ALL', 'L', 'P2', '8', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'P', 'GG', '90', 'E', 'P3', '15', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'P', 'GG', '91', 'E', 'P3', '16', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'P', 'HH', '92', 'E', 'P3', '17', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'GG', 'ALL', 'L', 'P3', '9', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'HH', 'ALL', 'L', 'P3', '10', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'JJ', '40', 'E', 'P4', '18', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'JJ', '41', 'E', 'P4', '19', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'JJ', '42', 'E', 'P4', '20', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'JJ', 'ALL', 'L', 'P4', '11', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'KK', '2', 'E', 'P5', '21', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'LL', '3', 'E', 'P5', '22', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'LL', '4', 'E', 'P5', '23', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'MM', '5', 'E', 'P5', '24', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'NN', '6', 'E', 'P5', '25', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'OO', '7', 'E', 'P5', '26', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'PP', '8', 'E', 'P5', '27', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'QQ', '24', 'E', 'P5', '28', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'RR', '25', 'E', 'P5', '29', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'KK', 'ALL', 'L', 'P5', '12', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'LL', 'ALL', 'L', 'P5', '13', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'MM', 'ALL', 'L', 'P5', '14', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'NN', 'ALL', 'L', 'P5', '15', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'OO', 'ALL', 'L', 'P5', '16', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'RR', 'ALL', 'L', 'P5', '19', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'PP', 'ALL', 'L', 'P5', '17', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'X', 'QQ', 'ALL', 'L', 'P5', '18', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'SS', '10', 'E', 'P6', '30', 'Y');

1 row created.

SQL> commit;

Commit complete.



If PREPRO ='N' Then

Update set STATS='R' WHERE STATS='W' AND TYPE='E';


Note: IF STATS= 'W' and TYPE='E' and PREPRO='N' contains Multiple records then update STATS AS 'R' for first record.
and for second record if the first record STATS='S' Then update second record STATS AS 'R'
similarly for third,fourth,fifth(one by one).

ELSIF

IF STATS= 'W' and TYPE='E' and PREPRO='Y' contains Multiple records then update STATS AS 'R' for first record.
and for second record if the first record STATS='S' Then update second record STATS AS 'R'
similarly for third,fourth,fifth(one by one).


And then IF STATS='S' WHERE TYPE='E' AND PROPTION='P1'(Contains Multiple records) Then

Update STATS AS 'R' for first record WHERE .STATS='W' WHERE TYPE='L' AND PROPTION='P1'
and for second record if the first record STATS='S' Then update second record STATS AS 'R',
similarly for third,fourth,fifth(one by one).

Similarly for PROPTION P2,P3,P4,P5 and P6

Please suggest me.How can i acheive this logic in single procedure?
Re: How to acheive this logic in single procedure? [message #392447 is a reply to message #392439] Wed, 18 March 2009 00:53 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
do not cross/multi-post
Re: How can i acheive this logic in single procedure? [message #392448 is a reply to message #392439] Wed, 18 March 2009 00:54 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: How can i acheive this logic in single procedure? [message #392449 is a reply to message #392439] Wed, 18 March 2009 00:58 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
need more information ....

regards
sriram.
Re: How can i acheive this logic in single procedure? [message #392452 is a reply to message #392439] Wed, 18 March 2009 01:19 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
First we have to update(one by one) STATS as 'R' iF the TYPE='E' and STATS='WAIT' and PREPRO='Y'.

Note: If the first record STATS='S' for the key combination TYPE='E' and STATS='WAIT' and PREPRO='Y'.

then update the second record STATS as 'R' for the same key combination.


For the TYPE 'L' ALL the STATS should be 'S' Where TYPE= 'E' and we have to update STATS As 'R' one by one as stated above for the key combination TYPE='L' and STATS='WAIT' and PREPRO='Y'.
Re: How can i acheive this logic in single procedure? [message #392453 is a reply to message #392439] Wed, 18 March 2009 01:22 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
So proceed to do so.
What is your problem?
Re: How to acheive this logic in single procedure? (merged) [message #392456 is a reply to message #392439] Wed, 18 March 2009 01:28 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
im getting stuck with processing one by one row and checking for previous record STATS.
need your ideas to proceed furthur.
Re: How to acheive this logic in single procedure? (merged) [message #392478 is a reply to message #392456] Wed, 18 March 2009 02:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Two remarks:
1. to store numeric data, use a numeric data type.
2. Oracle has no idea what you mean when you talk about 'first', 'second', etc. You need to tell Oracle (and us) how you want your data to be sorted. And again: if you sort by any column of your table, you are sorting alphanumeric values, this will most probably have undesired results.

MHE
Re: How to acheive this logic in single procedure? (merged) [message #392501 is a reply to message #392439] Wed, 18 March 2009 04:03 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
How can i check the multiple records STATS(Column) at one shot.

if i declare cursor it will retrieve multiple rows

IF STaTS='S' and TYPE='E' and PREPRO='Y'

Then

Update SET STATS='R' Where STaTS='W' and TYPE='L' and PREPRO='Y'

Re: How to acheive this logic in single procedure? (merged) [message #392509 is a reply to message #392501] Wed, 18 March 2009 04:24 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
select count(*)  into one_variable from your_table where your_conditions;


 if one_variable >1 then
write your requirements here.


now prepare your code.forum is just for help,and for giving clues...not for the spoon feeding.

try yourself and let us know what happend.

regards
sriram

[Updated on: Wed, 18 March 2009 04:27]

Report message to a moderator

Re: How to acheive this logic in single procedure? (merged) [message #392564 is a reply to message #392439] Wed, 18 March 2009 07:42 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
SQL> create or replace procedure sp_pre as
  2  V_COUNT Number;
  3  V_DCOUNT NUMBER;
  4  V_PRESTATS VARCHAR2(50);
  5  Cursor F is Select STATS, CODE, LOC, KLI, TYPE, PROPTION, ORDER_NO, PREPRO,
  6  ROWNUM as SEQORDER from SIMIS_DBA.TEST_NEW Where TYPE='E' 
  7  ORDER BY STATS, CODE, LOC, KLI, TYPE, PROPTION, ORDER_NO, PREPRO;
  8  cursor MS is select * from simis_dba.test_new WHere exists
  9  (select * from simis_dba.test_new Where STATS<>'S' AND TYPE='E');
 10  begin
 11  select count(*) into v_count from simis_dba.test_new Where TYPE='E';
 12  for S in F loop
 13  for CNT IN 1..v_count Loop
 14  IF CNT=1 Then
 15  UPDATE SIMIS_DBA.TEST_NEW SET STATS='R' WHERE TRIM(TYPE)=S.TYPE 
 16  and trim(PROPTION)=S.PROPTION and trim(CODE)=S.CODE AND TRIM(STATS)='A' 
 17  AND TRIM(LOC)=S.LOC AND ROWNUM=CNT;
 18  dbms_output.put_line('SEQUENCE ORDER is ' || S.SEQORDER);
 19  ELSE
 20  V_DCOUNT := CNT-1;
 21  Select STATS INTO V_PRESTATS FROM SIMIS_DBA.TEST_NEW WHERE TRIM(TYPE)=S.TYPE and 
 22  trim(PROPTION)=S.PROPTION and ROWNUM=V_DCOUNT;
 23  IF V_PRESTATS='S' Then
 24  UPDATE SIMIS_DBA.TEST_NEW SET STATS='R' WHERE TRIM(TYPE)=S.TYPE and trim(PROPTION)=S.PROPTION
 25  and trim(CODE)=S.CODE AND TRIM(STATS)='A' AND TRIM(LOC)=S.LOC AND ROWNUM=S.SEQORDER;
 26  ELSE
 27  dbms_output.put_line('SEQUENCE ORDER is ' || V_PRESTATS);
 28  END IF;
 29  END IF;
 30  end loop;
 31  END LOOP
 32  commit;
 33  end;
 34  /

Procedure created.

SQL> execute sp_pre
BEGIN sp_pre; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SIMIS_DBA.SP_PRE", line 21
ORA-06512: at line 1


SQL> 



I tried the above procedure to update one by one.
getting a error to select a value to the variable.
Re: How to acheive this logic in single procedure? (merged) [message #392568 is a reply to message #392439] Wed, 18 March 2009 07:50 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Suggest you read up on rownum, because it doesn't do what you think it does.
I also suggest that you indent your code and put blank lines between statements - makes it a lot easier to read.

EDIT: typo

[Updated on: Wed, 18 March 2009 07:51]

Report message to a moderator

Re: How to acheive this logic in single procedure? (merged) [message #392569 is a reply to message #392564] Wed, 18 March 2009 07:59 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I also suggest you read up on proper coding for procedures. Exception processing is part of writing code. Leave it out and you get errors such as yours.
Re: How to acheive this logic in single procedure? (merged) [message #392704 is a reply to message #392439] Thu, 19 March 2009 00:30 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
CREATE OR REPLACE PROCEDURE SIMIS_DBA.SP_PRE AS

V_COUNT TEST_NEW.ORDER_NO%TYPE;
V_DCOUNT TEST_NEW.ORDER_NO%TYPE;
V_PRESTATS TEST_NEW.STATS%TYPE;


CURSOR F IS Select STATS, CODE, LOC, KLI, TYPE, PROPTION, ORDER_NO, PREPRO,ROWNUM as SEQORDER from SIMIS_DBA.TEST_NEW Where TYPE='E';

BEGIN

SELECT COUNT(*) INTO V_COUNT FROM SIMIS_DBA.TEST_NEW WHERE TYPE='E';

FOR S in F LOOP


----------------------------UPDATE THE FIRST ROW STATS AS 'R'IF STATS='A'---------------------------------------------------------

IF S.SEQORDER=1 THEN

UPDATE SIMIS_DBA.TEST_NEW SET STATS='R' WHERE TRIM(TYPE)=S.TYPE 
AND TRIM(PROPTION)=S.PROPTION AND TRIM(CODE)=S.CODE AND TRIM(STATS)='A' AND ROWNUM=S.SEQORDER;
     
DBMS_OUTPUT.PUT_LINE('SEQUENCE ORDER IS ' || S.SEQORDER);



-----------------CHECK FOR PREVIOUS ROW STATS AND UPDATE STATS AS 'R' IF STATS='A' FOR CURRENT PROCESSING ROW---------------------

ELSE

FOR SEQ IN 2..V_COUNT LOOP

V_DCOUNT := SEQ-1;

SELECT STATS INTO V_PRESTATS FROM SIMIS_DBA.TEST_NEW WHERE TRIM(TYPE)=S.TYPE AND ROWNUM=V_DCOUNT;


IF V_PRESTATS='S' THEN

UPDATE SIMIS_DBA.TEST_NEW SET STATS='R' WHERE TRIM(TYPE)=S.TYPE AND TRIM(PROPTION)=S.PROPTION
AND TRIM(CODE)=S.CODE AND TRIM(STATS)='A' AND ROWNUM=SEQ;

ELSE

DBMS_OUTPUT.PUT_LINE('SEQUENCE ORDER IS ' || V_PRESTATS);


END IF;


END LOOP;     
END IF;
END LOOP;
COMMIT;
END;


How can i check for Previous column value in IF Statement using ROWNUM.
When i tried there is no row fetched.

required is Always a row to be fetch into variable.
but no data found to fetch.
i will handle the exception for this
i gone through the rownum and i removed the Order by Clause.

[Updated on: Thu, 19 March 2009 00:36]

Report message to a moderator

Re: How to acheive this logic in single procedure? (merged) [message #392783 is a reply to message #392568] Thu, 19 March 2009 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Wed, 18 March 2009 12:50
Suggest you read up on rownum, because it doesn't do what you think it does.



Rownum is not a property of rows in tables.
It is a property of rows outputted by queries.

Observe:
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> create table rownum_test as (select rownum id from dual connect by level <= 10);

Table created.

SQL> select id, rownum from (select id from rownum_test order by id asc);

        ID     ROWNUM
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10

10 rows selected.

SQL> select id, rownum from (select id from rownum_test order by id desc);

        ID     ROWNUM
---------- ----------
        10          1
         9          2
         8          3
         7          4
         6          5
         5          6
         4          7
         3          8
         2          9
         1         10

10 rows selected.

SQL> 


See how the same rows have different rownums?
You can't do what you're doing the way you're doing it.

I suspect you're missing a column that allows you to order the rows.

EDIT: fixed example to actually show what I'm talking about

[Updated on: Thu, 19 March 2009 05:05]

Report message to a moderator

Re: How to acheive this logic in single procedure? (merged) [message #392834 is a reply to message #392569] Thu, 19 March 2009 07:59 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
joy_division wrote on Wed, 18 March 2009 08:59
I also suggest you read up on proper coding for procedures. Exception processing is part of writing code. Leave it out and you get errors such as yours.


I guess you missed this message, so I'll just post it again, but then again, you're probably one of those people who thinks Exception Handling is just extra code that takes too much time and isn't worth the effort. Suit yourself.
Re: How to acheive this logic in single procedure? (merged) [message #392855 is a reply to message #392439] Thu, 19 March 2009 08:53 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think adding an exception handler at this point would only confuse matters.

It's this select that's giving no_data_found:
SELECT STATS INTO V_PRESTATS FROM SIMIS_DBA.TEST_NEW WHERE TRIM(TYPE)=S.TYPE AND ROWNUM=V_DCOUNT;

OP is not getting no_data_found because there is data legitimately missing, he's getting no_data_found because he doesn't understand how rownum works.
If he adds an exception handler chances are he's just going to obscure this fundamental bug.

OP needs to sort out the bit in bold before he does anything else because it's never going to work.
Previous Topic: Looking for API to change Saloes Person Info. of a customer
Next Topic: Without Loop (merged)
Goto Forum:
  


Current Time: Sun Dec 04 20:40:35 CST 2016

Total time taken to generate the page: 0.08850 seconds