| validate my function [message #562906] |
Mon, 06 August 2012 14:22  |
ora1980
Messages: 243
Registered: May 2008
|
Senior Member |
|
|
create table tbl_ann(id number, x number, y number);
insert into tbl_ann values(1,1,2);
insert into tbl_ann values(2,2,2);
insert into tbl_ann values(3,3,1);
insert into tbl_ann values(4,4,4);
commit;
select * from tbl_ann;
My requirement: when we select data from this table order by id,
I have to set a flag to Y or N based on the following rule
Iterate through ALL the records order by id, and look at the ratio of x:y, and check if the ratio is 1:1, If yes, set
the flag to Y else, set it to N with the following condition
But, if the flag becomes Y and then goes back to N, then our result should be N.
Here is the function I wrote, with above data, this function should return N, because if you loop through the ratio check 1: 1 (x/y) , we get
N
Y
N
Y
For id 2, the ratio is 1:1 (2/2) , so we flag as Y, but it has gone back to N (ratio for id 3 (3/1) is not 1:1)...anywhere result is going back to N, the function should return N, but once it is flagged as Y and the ratio continues to be 1:1,
function should return Y
I wrote this function which gives me a Y, result has to be N
CREATE OR REPLACE FUNCTION myfun
RETURN VARCHAR2 IS
v_result VARCHAR2 (1);
prev_result VARCHAR2 (1); -- v_result from previous row
CURSOR mycur IS
SELECT *
FROM tbl_ann
ORDER BY id;
BEGIN
FOR myrec IN mycur
LOOP
prev_result := v_result;
dbms_output.put_line (myrec.id || ' = myrec.id in myfun'); -- For debugging only
IF (myrec.x / NULLIF (myrec.y, 0)) = 1 THEN
v_result := 'Y';
ELSE
v_result := 'N';
IF prev_result = 'Y' THEN
v_result := 'N';
END IF;
END IF;
END LOOP;
RETURN v_result;
END myfun;
|
|
|
|
| Re: validate my function [message #562920 is a reply to message #562906] |
Mon, 06 August 2012 17:27  |
cookiemonster
Messages: 9284
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well this:
v_result := 'N';
IF prev_result = 'Y' THEN
v_result := 'N';
END IF;
does exactly the same as:
You don't need prev_result.
However I wouldn't write a for loop at all.
I'd find the max id where the ratio is 1 and compare it to the max id where the ratio isn't 1.
If the former is greater return Y, else return N.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
