Firebird stored procedure with indexed variable using execute statement

Question

How can I use indexed variable in a Firebird stored procedure? I mean, I have output parameters ODATE1, ODATE2, ODATE3, can I use as ':ODATE' || COUNTER to set the value in a loop?

I have 2 tables like this:

    1. T_EMPLOYEE
    ---------------
    | ID_E | NAME |
    ---------------
    | 1    | Anne |
    ---------------
    | 2    | Bob  |
    ---------------


    2. T_WORK
    ----------------------------
    | ID_W | DATE_W     | ID_E |
    ----------------------------
    | 1    | 2021-01-01 | 1    |
    ----------------------------
    | 2    | 2021-01-01 | 2    |
    ----------------------------
    | 3    | 2021-01-02 | 1    |
    ----------------------------
    | 4    | 2021-01-03 | 2    |
    ----------------------------

From that table I want to make a stored procedure to get this result:

    DASHBOARD
    -----------------------------------------------------------
    | OID_E | ONAME     | ODATE1     | ODATE2     | ODATE3     |
    ----------------------------------------------------------
    | 1     | Anne      |     1      |      1     |      0     |
    -----------------------------------------------------------
    | 2     | Bob       |     1      |      0     |      1     |
    -----------------------------------------------------------

I tried using EXECUTE STATEMENT like this in stored procedure:

    DECLARE VARIABLE COUNTER INT;
    BEGIN

    FOR 
        SELECT ID_E, NAME FROM T_EMPLOYEE 
        INTO :OID_E, :ONAME
    D0
    BEGIN
        COUNTER = 1;
        WHILE (COUNTER<=3) DO
        BEGIN 
           EXECUTE STATEMENT 'SELECT COUNT(*) FROM T_WORK WHERE DATE_W = ''2021-01-0' || COUNTER ||
           ''' AND ID_E = ' || :OID_E || ' INTO :ODATE' || COUNTER;
    
           COUNTER = COUNTER + 1;
        END 
        SUSPEND;
    END 
    
    END  /*procedure*/

The procedure can't be compiled. Then I tried the simple one like this without COUNTER index replacement:

    DECLARE VARIABLE COUNTER INT;
    BEGIN

    FOR 
        SELECT ID_E, NAME FROM T_EMPLOYEE 
        INTO :OID_E, :ONAME
    D0
    BEGIN
        COUNTER = 1;
        WHILE (COUNTER<=3) DO
        BEGIN 
           EXECUTE STATEMENT 'SELECT COUNT(*) FROM T_WORK WHERE ID_E = :OID_E ' ||
           ' AND DATE_W =''2021-01-02'' INTO :ODATE2';

           COUNTER = COUNTER + 1;
        END 
        SUSPEND;
    END 
    
    END  /*procedure*/

The procedure can be compiled, but when I execute, it will raise this error:

    SQL Error:  Dynamic SQL Error SQL error code = @1 Token unknown - line @1, column @2 @1. Error Code: -104. Invalid token

Please give me insight. How to use EXECUTE STATEMENT to make a flexible looping to set indexed variable. Or you have another solution for my needs.

Additional information: Firebird v2.5

Answer 1

You cannot dynamically reference PSQL variables (including parameters) like this. However, you don't need to jump through all these hoops to get the desired results.

You can use something like the following(which doesn't even need a procedure):

select e.ID_E as OID_E, e.NAME as ONAME, 
  count(case when w.DATE_W = date '2021-01-01' then 1 end) as ODATE1,
  count(case when w.DATE_W = date '2021-01-02' then 1 end) as ODATE2,
  count(case when w.DATE_W = date '2021-01-03' then 1 end) as ODATE3
from T_EMPLOYEE e
inner join T_WORK w
  on w.ID_E = e.ID_E
group by e.ID_E, e.NAME
order by e.ID_E

Fiddle: https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=59066afc0fd7f6b5cb87eac99164e899