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 Go to next message
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 #287743 is a reply to message #287741] Thu, 13 December 2007 06:09 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

if you could elobrate more on what is your sql query and what exactly are you trying to achive?we have no idea of what you actual purpose is.


regards,
Re: Transer sql query into oracle function [message #287751 is a reply to message #287741] Thu, 13 December 2007 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

can we just simply transer sql query into oracle function to get same result.

It depends on your query

Quote:

order by rownum

This is meaningless, you are ALWAYS order by rownum by definition (unless you explicitly give another order).

Regards
Michel

[Updated on: Thu, 13 December 2007 06:30]

Report message to a moderator

Re: Transer sql query into oracle function [message #287752 is a reply to message #287743] Thu, 13 December 2007 06:32 Go to previous messageGo to next message
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 #287769 is a reply to message #287752] Thu, 13 December 2007 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

select Decode(mod(Y,9999),0,9999,mod(Y,9999)) into X
from dual connect by level <1;

I stop there (the rest is unreadable).
What is the meaning of this?

Explain what you're trying to do.

Regards
Michel
Re: Transer sql query into oracle function [message #287786 is a reply to message #287741] Thu, 13 December 2007 08:14 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Your logic looks so complex .

Try to experiment on the following code at your own risk

SQL> select  level , level + 9997 ,
  2  Mod((level-1) + 9997, 9999)+1 A,
  3  TRUNC((((level + 9997)-100) /99 ) -1) C
  4   from  dual  connect by level <10;

     LEVEL LEVEL+9997          A          C
---------- ---------- ---------- ----------
         1       9998       9998         98
         2       9999       9999         98
         3      10000          1         99
         4      10001          2         99
         5      10002          3         99
         6      10003          4         99
         7      10004          5         99
         8      10005          6         99
         9      10006          7         99

9 rows selected.

SQL>



Please ensure whether this logic is handy with your logic.


Thumbs Up
Rajuvan.
Re: Transer sql query into oracle function [message #287864 is a reply to message #287769] Thu, 13 December 2007 20:11 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
Quote:

select Decode(mod(Y,9999),0,9999,mod(Y,9999)) into X
from dual connect by level <1;



This function (A_PAY_BATCH_NO_SEQ) is used to recycle X when it reach 9999 and its work fine. But the problem is function B_PAY_BATCH_NO_SEQ. Maybe using function is not a good idea here Sad
Re: Transer sql query into oracle function [message #287866 is a reply to message #287864] Thu, 13 December 2007 20:31 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
There is no different when we using 'level' or 'rownum'. Is it right?
Re: Transer sql query into oracle function [message #287899 is a reply to message #287866] Fri, 14 December 2007 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Wrong.
And I still don't understand what is the logic behind this statement.
Can you explain me?
What is the meaning of "connect by level < 1"?

Regards
Michel
Re: Transer sql query into oracle function [message #287924 is a reply to message #287899] Fri, 14 December 2007 01:20 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
It's ok if i not put "connect by level < 1". Not sure why i put there. Confused
Re: Transer sql query into oracle function [message #287930 is a reply to message #287924] Fri, 14 December 2007 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't know, you didn't explain what you want to do with this.

Regards
Michel
Re: Transer sql query into oracle function [message #287937 is a reply to message #287930] Fri, 14 December 2007 01:30 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Unique Constraint on a Table
Next Topic: varray in in-list function
Goto Forum:
  


Current Time: Wed Feb 12 04:46:34 CST 2025