CODEWITHSUNDEEP
oracle-databaseplsql
civesuas_sine
civesuas_sine

Reputation: 119

oracle function not returning correct value

I have this function which data from select needs to compare with conditions i made in IF and it works in case when i have one row in select, then it returns ret_val = 0.

But if there are two or more rows which one of them fulfills condition and one does not, still it doesn't return 0 ,it returns rec_tkt_sess.cash_out.

Can someone tell why cursor in IF doesn't return 0 with these data bellow?

Like it cursor does not go through IF with all rows...

enter image description here

CREATE OR REPLACE FUNCTION cash_out_ticket_cond(party_id   IN casino_users.party_id%TYPE ,session_id IN bus_session.session_id%TYPE    

    ) RETURN NUMBER AS ret_val NUMBER; 

    P_EXCHANGE_BET_CREDITS COUNTRY.EXCHANGE_BET_CREDITS%type;
    P_EXCHANGE_VALUE    COUNTRY.EXCHANGE_VALUE%type;


CURSOR cur_tkt_sess (party_id IN casino_users.party_id%TYPE,session_id IN .bus_session.session_id%TYPE)
        IS
        SELECT bs.session_id 
                ,tii.status 
                ,ROW_NUMBER() OVER (PARTITION BY bs.session_id ORDER BY status ASC)  rn 
                ,NVL(TO_CHAR(bs.started, 'DD.MM.YYYY HH24:MI'), 'Live')  started 
                ,bs.bet  bet 
                ,tii.time_p 
                ,tii.live_prematch 
                ,cash_out(bet)  cash_out 
            FROM bus_session bs 
                ,ticket_items tii 
        WHERE bs.session_id             = tii.bus_session_session_id 
            AND bs.session_type         = 'TICKET SESSION' 
            AND bs.party_id             = cur_tkt_sess.party_id 
            AND bs.session_id           = cur_tkt_sess.session_id 
            AND NVL(bs.session_close, 'N') = 'N';           
    rec_tkt_sess cur_tkt_sess%ROWTYPE; 

    BEGIN       
        CHAGE_CREDITS (party_id, P_EXCHANGE_BET_CREDITS, P_EXCHANGE_VALUE);     
    OPEN cur_tkt_sess(cash_out_ticket_cond.party_id, cash_out_ticket_cond.session_id); 
            FETCH cur_tkt_sess 
    INTO rec_tkt_sess; 

    IF(cur_tkt_sess%FOUND) THEN         
        IF( 
            (TO_DATE(rec_tkt_sess.started,'DD.MM.YYYY HH24:MI:SS') +1 <=SYSDATE)  -- is ticket older then 24 hours
            OR 
            (rec_tkt_sess.live_prematch != '0')  --is it live  (1 live , 0 not live)
            OR 
            (rec_tkt_sess.time_p <SYSDATE)  -- is game begin
            )
            THEN 
                ret_val := 0;
            ELSE 
            ret_val := rec_tkt_sess.cash_out;           
        END IF; 

    ELSE    
        ret_val := -1; 
    END IF; 

    RETURN(ret_val); 

    END cash_out_ticket_cond;

Upvotes: 1

Views: 166

Answers (1)

Stilgar
Stilgar

Reputation: 461

You are only FETCHING one row and without having an ORDER BY clause you have no idea which of your two sample rows will be FETCHED. After adding ORDER BY, cycle through the results until you reach the condition you are looking for:

OPEN cur_tkt_sess;
LOOP
    FETCH cur_tkt_sess INTO rec_tkt_sess;
    EXIT WHEN cur_tkt_sess%NOTFOUND;
    --[ Check some stuff, Do some stuff ]--
END LOOP
CLOSE cur_tkt_sess

Upvotes: 2

Related Questions