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
ora1980
Messages: 246
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 Go to previous message
cookiemonster
Messages: 10846
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: Regarding inserting CSV files in oracle database
Next Topic: How to display the result
Goto Forum:
  


Current Time: Fri Jul 25 16:22:57 CDT 2014

Total time taken to generate the page: 0.06969 seconds