Home » SQL & PL/SQL » SQL & PL/SQL » Transer sql query into oracle function
Transer sql query into oracle function [message #287741] |
Thu, 13 December 2007 06:03  |
ednms
Messages: 39 Registered: November 2007
|
Member |
|
|
Hi, i have question about oracle function.
can we just simply transer sql query into oracle function
to get same result.
if in my query it involved order by rownum,
so in my oracle function should i pass the rownum ('order by pa_rownum') or just put 'order by rownum'
|
|
|
|
|
Re: Transer sql query into oracle function [message #287752 is a reply to message #287743] |
Thu, 13 December 2007 06:32   |
ednms
Messages: 39 Registered: November 2007
|
Member |
|
|
Ok. This is my sql query
SELECT rownum,A_PAY_BATCH_NO_SEQ(rownum,9997) A
,DECODE(mod(SUM(DECODE(MOD(A_PAY_BATCH_NO_SEQ(rownum,9997), 9999),1,1,0)) OVER (ORDER BY rownum) + 99,99)
,0,99
,mod(SUM(DECODE(MOD(A_PAY_BATCH_NO_SEQ(rownum,9997), 9999),1,1,0)) OVER (ORDER BY rownum) + 99,99)) C
from dual connect by level < 10
and 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;
Y NUMBER := pa_rownum + pa_last_seq_no;
begin
select Decode(mod(Y,9999),0,9999,mod(Y,9999)) into X
from dual connect by level <1;
RETURN X;
end;
/
i'm trying to put 'C' into Oracle Function (B_PAY_BATCH_NO_SEQ_TEST) so i can just call like this
SELECT rownum
,A_PAY_BATCH_NO_SEQ(rownum,9997) A
,B_PAY_BATCH_NO_SEQ_TEST(rownum,9997,99) C
from dual connect by level < 10
my B_PAY_BATCH_NO_SEQ_TEST is as below
CREATE OR REPLACE FUNCTION "B_PAY_BATCH_NO_SEQ_TEST"
( pa_rownum IN NUMBER
, pa_last_seq_no IN NUMBER
, pa_bon IN NUMBER)
RETURN VARCHAR2
IS
X NUMBER;
Y NUMBER;
Z NUMBER := pa_rownum + pa_last_seq_no;
U NUMBER := pa_bon;
begin
SELECT DECODE(mod(SUM(DECODE(MOD(A_PAY_BATCH_NO_SEQ(pa_rownum,pa_last_seq_no), 9999),1,1,0)) OVER (ORDER BY pa_rownum) + U,99)
,0,99
,mod(SUM(DECODE(MOD(A_PAY_BATCH_NO_SEQ(pa_rownum,pa_last_seq_no), 9999),1,1,0)) OVER (ORDER BY pa_rownum) + U,99)) into X
from dual
CONNECT BY LEVEL < 1;
RETURN X;
end;
/
this is my result in sql query
ROWNUM A C
1 9998 98
2 9999 98
3 1 99
4 2 99
5 3 99
6 4 99
7 5 99
8 6 99
9 7 99
and using oracle function
ROWNUM A C
1 9998 99
2 9999 99
3 1 1
4 2 99
5 3 99
6 4 99
7 5 99
8 6 99
9 7 99
my result for C is not same when i use oracle function.
[Updated on: Thu, 13 December 2007 06:39] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
Re: Transer sql query into oracle function [message #287937 is a reply to message #287930] |
Fri, 14 December 2007 01:30   |
ednms
Messages: 39 Registered: November 2007
|
Member |
|
|
First i try this
Quote: |
select Decode(mod(level,9),0,9,mod(level,9))
from dual connect by level <20;
|
Then i put it in oracle function so just pass Y = level in my fuction. I just copy exactly the query and put it in oracle function. Because oracle function can only return 1 value so i thought i have to put 'connect by level <1'
Quote: |
select Decode(mod(Y,9999),0,9999,mod(Y,9999)) into X
from dual connect by level <1;
|
|
|
|
Re: Transer sql query into oracle function [message #287948 is a reply to message #287937] |
Fri, 14 December 2007 01:46  |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> select * from dual connect by level <1;
D
-
X
1 row selected.
SQL> select * from dual;
D
-
X
1 row selected.
More, don't write a SQL query inside PL/SQL if you can directly do it in PL/SQL => use CASE or IF.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Wed Feb 12 04:46:34 CST 2025
|