Home » SQL & PL/SQL » SQL & PL/SQL » Recycle number
Recycle number [message #285927] Thu, 06 December 2007 02:06 Go to next message
ednms
Messages: 39
Registered: November 2007
Member
Hi, i need some advise about my oracle function.

CREATE OR REPLACE FUNCTION "A_PAY_BATCH_NO_SEQ"
( pa_rownum IN NUMBER
, pa_last_seq_no IN NUMBER)
RETURN VARCHAR2
IS
X NUMBER := 1;
begin

select pa_rownum + pa_last_seq_no into X
from dual;

if(X > 9) then
X := X - 9;
end if;

RETURN X;

end;
/


X new X
---------
1 1
2 2
3 3
4 4
5 5 cycle 1
6 6
7 7
8 8
9 9
----------
10 1
11 2
12 3
13 4
14 5 cycle 2
15 6
16 7
17 8
18 9
----------
19 1
20 2
21 3
22 4
23 5 cycle 3
24 6
25 7
26 8
27 9
----------
28 1
29 2 cycle 4
30 3
31 4

I have list of X, I want X to be recycled whenever X > 9 (new X)
my function work only for until cycle 2 because X := X - 9.
I need some logic here.
Please advise.Thanks.
Re: Recycle number [message #285930 is a reply to message #285927] Thu, 06 December 2007 02:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use mod.

Quote:

select pa_rownum + pa_last_seq_no into X
from dual;

Don't do that. Use a direct assignment. Using SQL for this is a waste of resources.
Re: Recycle number [message #285931 is a reply to message #285927] Thu, 06 December 2007 02:12 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

don't know whether i understood you correctly.do you mean to say that once your value of X reaches 9.it should again start with 1.

regards,
Re: Recycle number [message #285933 is a reply to message #285927] Thu, 06 December 2007 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select level, mod(level-1,9)+1 x from dual connect by level < 30;
     LEVEL          X
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10          1
        11          2
        12          3
        13          4
        14          5
        15          6
        16          7
        17          8
        18          9
        19          1
        20          2
        21          3
        22          4
        23          5
        24          6
        25          7
        26          8
        27          9
        28          1
        29          2

29 rows selected.

Regards
Michel
Re: Recycle number [message #285934 is a reply to message #285927] Thu, 06 December 2007 02:23 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

To be more precise Use Mod + Decode

SQL>   select level x , Decode(mod(level,9),0,9,mod(level,9)) new_x
 from dual connect by level <32;
         X      NEW_X
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10          1
        11          2
        12          3
        13          4
        14          5
        15          6
        16          7
        17          8
        18          9
        19          1
        20          2
        21          3
        22          4
        23          5
        24          6
        25          7
        26          8
        27          9
        28          1
        29          2
        30          3
        31          4

31 rows selected.

SQL>


Oops.. I am always late Sad

Thumbs Up
Rajuvan

[Updated on: Thu, 06 December 2007 02:25]

Report message to a moderator

Re: Recycle number [message #285957 is a reply to message #285934] Thu, 06 December 2007 03:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ah, two spoonfeeders.. Will be hard for the OP to choose..
Re: Recycle number [message #285959 is a reply to message #285927] Thu, 06 December 2007 03:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hmhm ..

Are you accusing Michel also 'Spoonfeeder' ? Shocked

I can bear if you call me spoonfeeder , But not Michel !!!!

No Laughing

Thumbs Up
Rajuvan.
Re: Recycle number [message #285966 is a reply to message #285927] Thu, 06 December 2007 03:44 Go to previous message
ednms
Messages: 39
Registered: November 2007
Member
Thanks.
Previous Topic: Execute Plan About My Sqls
Next Topic: Dropping index
Goto Forum:
  


Current Time: Wed Dec 07 20:37:38 CST 2016

Total time taken to generate the page: 0.13657 seconds