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

What exactly are you trying to achieve? And how are you executing this, because apparently whatever you're using isn't correctly rendering Firebird error messages. In any case, one of the problems is that you have an `INTO` clause in the statement text you pass to `EXECUTE STATEMENT`. The statements passed to `EXECUTE STATEMENT` should be DSQL, while `INTO` is only valid in PSQL. You need to use `INTO` as a clause to [`EXECUTE STATEMENT`](https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-execstmt) itself. — Mark Rotteveel, Jul 15 '21 at 13:59
In any case, looking at tables and desired output, you don't even need `EXECUTE STATEMENT`, you could do this with a single query (even without using a stored procedure). — Mark Rotteveel, Jul 15 '21 at 14:06
transposition (pivot table, chess-table like it was called in USSR) better be do in client application (report generator, etc) than RDBMS. It is just the task applications are suited for and DB engines are not — Arioch 'The, Jul 16 '21 at 08:49
Hi @MarkRotteveel, actually I want to make a table of a 31 day in month which show the time of work of my employee.. the example I show here using a single date column in t_work, but here i have two column with start and end date. thank for your simple answer below, I will try to adapt to check wether the date is between date in t_work. thank you very much.. I will info the result soon.. — Umar Gumai, Jul 16 '21 at 12:28
And that is exactly what you should not do in database servers. It is using wrong tool for work, like drinking tea with a fork. This question actually seems duplicate of https://stackoverflow.com/questions/48262968/crosstab-counting-the-same-string-in-a-field-and-display-it-as-field-name and of https://stackoverflow.com/questions/55449169/is-there-any-way-to-pivot-rows-to-columns-dynamically-without-a-specific-no-of-c and of many others — Arioch 'The, Jul 16 '21 at 18:18

Mark Rotteveel · Answer 1 · 2021-07-15T14:45:51.777

0

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

edited Jul 15 '21 at 14:45

answered Jul 15 '21 at 14:26

Mark Rotteveel

100,966
191
140
197

Thank you very much. I have seen the fiddle you post.. its done with a simple code.. but i need to adapt it a little.. because actually in t_work i have two date column, start and end date.. i want to show a 31 day table wheter the employee work in each day.. I'll inform you soon – Umar Gumai Jul 16 '21 at 12:31
@UmarGumai I answered your original question. If you need something else, then I suggest you post a new question. Questions on Stack Overflow should not be a moving target. – Mark Rotteveel Jul 16 '21 at 12:54
thank you very much, yes I have gotten your answer of my original question. It work well. Your simple solution safe my life. – Umar Gumai Aug 08 '21 at 16:21

Firebird stored procedure with indexed variable using execute statement

1 Answers1