Home » SQL & PL/SQL » SQL & PL/SQL » validate my function
validate my function [message #562906] Mon, 06 August 2012 14:22 Go to next message
Messages: 251
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);
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


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

    v_result  VARCHAR2 (1);
    prev_result  VARCHAR2 (1);  -- v_result from previous row
    CURSOR  mycur IS
              SELECT    * 
          FROM  tbl_ann
             ORDER BY  id;
    FOR myrec IN mycur 
  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';
      v_result := 'N';
      IF  prev_result = 'Y'  THEN
        v_result := 'N';
      END IF;
        END IF;
    RETURN v_result;
END  myfun;

Re: validate my function [message #562920 is a reply to message #562906] Mon, 06 August 2012 17:27 Go to previous message
Messages: 12828
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:
      v_result := 'N';

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.
Previous Topic: subquey execution
Next Topic: How to display the result
Goto Forum:

Current Time: Wed Aug 23 18:26:15 CDT 2017

Total time taken to generate the page: 0.10033 seconds