CODEWITHSUNDEEP
informix
P.S.Mahalakshmi
P.S.Mahalakshmi

Reputation: 19

Store the result of sql and process it in informix

We have a view which contains 2 columns: pattern_start_time, pattern_end_time. The select query in the function will convert it to minutes and using that result we are processing to get the shift unused coverage.The function is getting created but the processing is not happening and getting the below error: SQLError[IX000]:Routine (my_list) cant be resolved. Also please enter image description heresuggest to loop till the length of the result.

CREATE function myshifttesting(orgid int) returning int;
    DEFINE my_list LIST( INTEGER not null );
    DEFINE my_list1 LIST( INTEGER not null );
    define i, j, sub, sub1 int;
    define total int;
    TRACE ON;
    TRACE 'my testing starts';
    INSERT INTO TABLE( my_list ) 
        select
    ((extend(current, year to second) + (dots.v_shift_coverage.pattern_start_time - datetime(00:00) hour to minute) - current)::interval minute(9) to minute)::char(10)::INTEGER 
        from
            dots.v_shift_coverage 
        where
            org_guid = orgid;
    INSERT INTO TABLE( my_list1 ) 
        select
    ((extend(current, year to second) + (dots.v_shift_coverage.pattern_end_time - datetime(00:00) hour to minute) - current)::interval minute(9) to minute)::char(10)::INTEGER 
        from
            dots.v_shift_coverage 
        where
            org_guid = orgid;
    let sub = 0;
    let sub1 = 0;
    let total = 0;
    for j = 0 to 4
        if (my_list(j) < my_list1(j)) 
        then
            if (my_list(j + 1) > my_list1(j)) 
            then
                let sub = sub + my_list(j + 1) - my_list1(j);
            end if;
        end if;
    end for
    if (my_list(0) > my_list1(4)) 
    then
        let sub1 = my_list(0) - my_list1(4);
    end if;
    let total = sub + sub1;
    return total;

end function;

Upvotes: 1

Views: 275

Answers (1)

Lu&#237;s Marques
Lu&#237;s Marques

Reputation: 1451

The error that you are receiving is because my_list(j) is not valid Informix syntax to access a LIST element. Informix is interpreting my_list(j) as a call to a function named my_list.

You can use a temporary table to "emulate" an array with your logic, something like this:

CREATE TABLE somedata
(
    letter1 CHAR( 2 ),
    letter2 CHAR( 2 )
);
INSERT INTO somedata VALUES ( 'a1', 'a2' );
INSERT INTO somedata VALUES ( 'b1', 'b2' );
INSERT INTO somedata VALUES ( 'c1', 'c2' );
INSERT INTO somedata VALUES ( 'd1', 'd2' );
INSERT INTO somedata VALUES ( 'e1', 'e2' );


DROP FUNCTION IF EXISTS forloop;

CREATE FUNCTION forloop()
RETURNING CHAR( 2 ) AS letter1, CHAR( 2 ) AS letter2;

    DEFINE number_of_rows INTEGER;
    DEFINE iterator       INTEGER;
    DEFINE my_letter1     CHAR( 2 );
    DEFINE my_letter2     CHAR( 2 );

    -- Drop temp table if it already exists in the session
    DROP TABLE IF EXISTS tmp_data;
    CREATE TEMP TABLE tmp_data
    (
        tmp_id SERIAL,
        tmp_letter1 CHAR( 2 ),
        tmp_letter2 CHAR( 2 )
    );
    
    -- Insert rows into the temp table, serial column will be the access key
    INSERT INTO tmp_data
    SELECT 0, 
        d.letter1,
        d.letter2
    FROM somedata AS d
    ORDER BY d.letter1;
    
    -- Get total rows of temp table
    SELECT COUNT( * ) 
    INTO number_of_rows
    FROM tmp_data;

    FOR iterator = 1 TO number_of_rows
        SELECT d.tmp_letter1
        INTO my_letter1
        FROM tmp_data AS d
        WHERE d.tmp_id = iterator;
        -- Check if not going "out of range"
        IF iterator < number_of_rows THEN
            SELECT d.tmp_letter2
            INTO my_letter2
            FROM tmp_data AS d
            WHERE d.tmp_id = iterator + 1;
        ELSE
            -- iterator + 1 is "out of range", return to the beginning
            SELECT d.tmp_letter2
            INTO my_letter2
            FROM tmp_data AS d
            WHERE d.tmp_id = 1;        
        END IF;
        
        RETURN my_letter1, my_letter2 WITH RESUME;
        
    END FOR;

END FUNCTION;

-- Running the function
EXECUTE FUNCTION forloop();

-- Results
letter1 letter2

a1      b2
b1      c2
c1      d2
d1      e2
e1      a2

 5 row(s) retrieved.

Upvotes: 2

Related Questions