Home » SQL & PL/SQL » SQL & PL/SQL » qery problem (oracle 9i)
qery problem Fri, 06 February 2009 03:48
 vivek_rol Messages: 65Registered: February 2009 Member
DEAR FREINDS
I AM USING BELOW FUNCTION TO CALCULATE
2ND HIGHST AMT BALANCE OF CUSTOMER.

AID (CUSTOMERID) WHICH IS IN PARAMETER OF FUNCTION
LED IS THE CUSTOMER AMT BALANCE TABLE
BAL IS CUSTOMER AMOUNT BALANCE WHICH IS COLUMN OF LED TABLE

FIRST METHOD 1)
CREATE OR REPLACE FUNCTION FN_GET_BAL(AID varchar2)
RETURN NUMBER
AUTHID CURRENT_USER AS
D1 NUMBER;
BEGIN

SELECT BAL INTO D1 FROM (
SELECT BAL FROM (
SELECT
BAL
FROM LED WHERE CUSTOMERID=AID
ORDER BY DATE_OF_TRANSACTION DESC)
WHERE ROWNUM<=2
MINUS
SELECT BAL FROM (
SELECT
BAL
FROM LED WHERE CUSTOMERID=AID
ORDER BY DATE_OF_TRANSACTION DESC)
WHERE ROWNUM<=1);

RETURN D1;
EXCEPTION
when others then
D1 :=null;
RETURN D1;
END;
/

IF I USE BELOW QUERY ON SESSION,
IT WILL GENERATE THE OUTPUT WITHIN FRACTION OF SECOND
BUT IF I USE THE SAME QUERY IN FUNCTION (WHICH I ALREADY USE IN ABOVE FINCTION),
ITS GOING IN LONG OPERATION
QUERY:
SELECT BAL FROM (
SELECT
BAL
FROM LED WHERE CUSTOMERID=AID
ORDER BY DATE_OF_TRANSACTION DESC)
WHERE ROWNUM<=2
MINUS
SELECT BAL FROM (
SELECT
BAL
FROM LED WHERE CUSTOMERID=AID
ORDER BY DATE_OF_TRANSACTION DESC)
WHERE ROWNUM<=1

SECOND METHOD 2) by using rnk

SELECT BAL FROM (
SELECT BAL,RANK() OVER (ORDER BY DATE_OF_TRANSACTION DESC) RNK FROM LED
WHERE CUSTOMERID='CA01') WHERE RNK=2

this is another method that is i am using to calculate second highest balance of customer.
here also i am facing same problem
if i run this on session , it will geberate the output within fraction of time
but if use this in function, it will go in long operation.

Re: qery problem [message #385025 is a reply to message #385024] Fri, 06 February 2009 03:51
 Michel Cadot Messages: 64953Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Use the query.

Don't post in UPPER case.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: qery problem [message #385031 is a reply to message #385024] Fri, 06 February 2009 04:41
 ashoka_bl Messages: 398Registered: November 2006 Location: Bangalore Senior Member
Hi,

It is better to use query ( The query using analytical function)

Regards,
Ashoka BL
Re: qery problem [message #385032 is a reply to message #385031] Fri, 06 February 2009 04:45
 trivendra Messages: 211Registered: October 2007 Location: Phoenix Senior Member
Yes. In this case it is good to use Analytical Function Query.
Re: qery problem [message #385033 is a reply to message #385031] Fri, 06 February 2009 04:47
 vivek_rol Messages: 65Registered: February 2009 Member
but i have to use function only but the problem is if u use function then it is going in long operation
Re: qery problem [message #385034 is a reply to message #385025] Fri, 06 February 2009 04:48
 vivek_rol Messages: 65Registered: February 2009 Member
i have to use the function along with select statement,
but the problem is it is taking lot time
Re: qery problem [message #385035 is a reply to message #385033] Fri, 06 February 2009 04:48
 trivendra Messages: 211Registered: October 2007 Location: Phoenix Senior Member
Re: qery problem [message #385036 is a reply to message #385033] Fri, 06 February 2009 04:49
 ThomasG Messages: 3201Registered: April 2005 Location: Heilbronn, Germany Senior Member
That's the way it is.

If you want to be faster you have to use the verison that is faster.

If you use the slower version it's slower.

 Quote: but i have to use function only

Which brings up the question: WHY?
Re: qery problem [message #385037 is a reply to message #385035] Fri, 06 February 2009 04:50
 vivek_rol Messages: 65Registered: February 2009 Member
i want to generate some reports base on this function thats why
Re: qery problem [message #385039 is a reply to message #385033] Fri, 06 February 2009 04:50
 Michel Cadot Messages: 64953Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
 vivek_rol wrote on Fri, 06 February 2009 11:47 but i have to use function only but the problem is if u use function then it is going in long operation

Use the query inside the function and get rid of the whole code.

Regards
Michel

Re: qery problem [message #385041 is a reply to message #385039] Fri, 06 February 2009 04:54
 vivek_rol Messages: 65Registered: February 2009 Member
Dear michal sir,
i did that only, i use that query in function?
still that function is taking a lot time on session
i am still confuse why this is happening?
query runns very well on session but if u use the same query inside function, function goes into long operation
Re: qery problem [message #385069 is a reply to message #385024] Fri, 06 February 2009 05:56
 vivek_rol Messages: 65Registered: February 2009 Member
whats the solution for this
Re: qery problem [message #385073 is a reply to message #385041] Fri, 06 February 2009 06:04
 Michel Cadot Messages: 64953Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
 Michel Cadot wrote on Fri, 06 February 2009 10:51 Use SQL*Plus and copy and paste your session. Regards Michel

And don't forget to format it.

Regards
Michel

Re: qery problem [message #385079 is a reply to message #385073] Fri, 06 February 2009 06:22
 trivendra Messages: 211Registered: October 2007 Location: Phoenix Senior Member
Are you using this function in some query where data is very large.If so have a look at this

```SQL> COLUMN CUSTOMERID FORMAT A12
SQL> COLUMN BAL FORMAT A12
SQL> COLUMN DATE_OF_TRANSACTION FORMAT A20
SQL> CREATE TABLE TEST_FOR_FUNCTION
(
CUSTOMERID           VARCHAR2(3 BYTE),
BAL                  NUMBER(10),
DATE_OF_TRANSACTION  DATE
)
Table created.
Elapsed: 00:00:00:23

SQL> INSERT INTO test_for_function
SELECT     DBMS_RANDOM.STRING ('U', 3), ABS (DBMS_RANDOM.random),
SYSDATE + LEVEL
FROM DUAL
CONNECT BY LEVEL <= 100000
100000 rows created.
Elapsed: 00:00:09:09

SQL> SELECT *
FROM test_for_function
WHERE customerid = 'ABC'

CUSTOMERID            BAL DATE_OF_TRANSACTION
------------ ------------ --------------------
ABC             218208103 12-JAN-17
ABC             623308774 05-DEC-47
ABC            1435747492 11-DEC-75
ABC             147434851 16-MAR-25
ABC             464380799 02-MAR-41

5 rows selected.
Elapsed: 00:00:01:85

SQL> -- Creating the function By you script
SQL> CREATE OR REPLACE FUNCTION fn_get_bal (aid VARCHAR2)
RETURN NUMBER AUTHID CURRENT_USER
AS
d1   NUMBER;
BEGIN
SELECT bal
INTO d1
FROM (SELECT bal
FROM (SELECT   bal
FROM test_for_function
WHERE customerid = aid
ORDER BY date_of_transaction DESC)
WHERE ROWNUM <= 2
MINUS
SELECT bal
FROM (SELECT   bal
FROM test_for_function
WHERE customerid = aid
ORDER BY date_of_transaction DESC)
WHERE ROWNUM <= 1);

RETURN d1;
EXCEPTION
WHEN OTHERS
THEN
d1 := NULL;
RETURN d1;
END;
Function created.
Elapsed: 00:00:00:25

SQL> -- Runing By Function
SQL> SELECT fn_get_bal ('ABC')
FROM DUAL

FN_GET_BAL('ABC')
-----------------
147434851

1 row selected.
Elapsed: 00:00:01:50

SQL> -- Runing By Analytic Function Query
SQL> SELECT bal
FROM (SELECT bal, RANK () OVER (ORDER BY date_of_transaction DESC) rnk
FROM test_for_function
WHERE customerid = 'ABC')
WHERE rnk = 2

BAL
------------
147434851

1 row selected.
Elapsed: 00:00:00:45```

This done by your Query. Now try with analytical function query.

```SQL> CREATE OR REPLACE FUNCTION fn_get_bal (aid VARCHAR2)
RETURN NUMBER AUTHID CURRENT_USER
AS
d1   NUMBER;
BEGIN
SELECT bal
INTO d1
FROM (SELECT bal
FROM (SELECT bal,
RANK () OVER (ORDER BY date_of_transaction DESC)
rnk
FROM test_for_function
WHERE customerid = aid)
WHERE rnk = 2);

RETURN d1;
EXCEPTION
WHEN OTHERS
THEN
d1 := NULL;
RETURN d1;
END;
Function created.
Elapsed: 00:00:01:28

SQL> SELECT fn_get_bal ('ABC')
FROM DUAL

FN_GET_BAL('ABC')
-----------------
147434851

1 row selected.
Elapsed: 00:00:00:45

SQL> SELECT bal
FROM (SELECT bal, RANK () OVER (ORDER BY date_of_transaction DESC) rnk
FROM test_for_function
WHERE customerid = 'ABC')
WHERE rnk = 2

BAL
----------
147434851

1 row selected.
Elapsed: 00:00:01:42```

Now Checking this function in Loop for 10 different customers;

```SQL> DECLARE
temp   PLS_INTEGER;
BEGIN
FOR i IN (SELECT     DBMS_RANDOM.STRING ('U', 3) AS parameter_value
FROM DUAL
CONNECT BY LEVEL <= 10)
LOOP
temp := fn_get_bal (i.parameter_value);
DBMS_OUTPUT.put_line (   'CUSTOMERID = '
|| i.parameter_value
|| ', Amount = '
|| temp
);
temp := NULL;
END LOOP;
END;

CUSTOMERID = VPK, Amount = 1107167281
CUSTOMERID = ECR, Amount = 501286546
CUSTOMERID = WKF, Amount = 261721190
CUSTOMERID = WWG, Amount = 786540982
CUSTOMERID = TTL, Amount = 253135236
CUSTOMERID = NHN, Amount = 137470996
CUSTOMERID = WHP, Amount = 1081470174
CUSTOMERID = KOS, Amount = 1467415258
CUSTOMERID = PZX, Amount = 480841720
CUSTOMERID = ZNY, Amount = 1251977604

PL/SQL procedure successfully completed.
Elapsed: 00:00:03:64```

When I have used this function in loop then time is increased. If you are using this function dont go it, try to implement the logic in your query with the use of analytic function.

Thanks
Trivendra

[Updated on: Fri, 06 February 2009 06:23]

Report message to a moderator

Re: qery problem [message #385218 is a reply to message #385024] Sat, 07 February 2009 20:24
 Barbara Boehmer Messages: 8713Registered: November 2002 Location: California, USA Senior Member
You might check out the example of a secondmax user-defined aggregate function in the 9i documentation in the link below. All you have to do is copy and paste the code to create the function, then you can use it to get the second maximum value just like you would use max to get the maximum value.