Db2 – Concat a string to a variable to use as a column name in a query

db2stored-procedures

I have a procedure where I query table a and create variables like this

SELECT TRCODE , COLA , COLB , COLC , COLD
INTO TRANSACTION_TYPE , HOURS , RATE , FACTOR , APPROPRIATION
FROM PR . PREXEC
WHERE USRID = USER_NUMBER ;

The appropriation variable contains one character and it is a number from 1-4.

I have another table called pr.earnings. In that table I have a columns named APPROP1 through APPROP4. In this same procedure I need to query pr.earnings for by taking the string 'APPROP' and concatenating the APPROPRIATION variable from the query.

I tried

select concat('APPROP',APPROPRIATION)

FROM PR.EARNIGS;

The result I want is select APPROP1 FROM PR.EARNINGS; What happens is I get a system created column with APPROP1 inside it. I can't figure out how to concat a string to a variable to use in a query.

I tried a using dynamic sql but kept getting errors because I don't know what I'm doing.
How do I concat a string and a variable to use it as a column name in an sql query?

Based on what Lennart suggested I have this now

SET STMT = 'SELECT APPROP' || appropriation || ' pr.earnings into ' || active_appropriation ||
               ' where empnumber = ' || user_number;
    EXECUTE IMMEDIATE STMT

And am trying to insert active_appropriation like this

insert into pr.worksheet (W_EMPLOYEE_NUMBER, W_TRANSACTION_TYPE, W_HOURS, W_RATE, W_APPROPRIATION)
    values (user_number, transaction_type, hours, rate, active_appropriation);

I get the error 'Variable cannot be null' I am assuming it is the active_appropriation value.

I got it to work. here is my solution.

    SET STMT2 = 'VALUES (SELECT approp' || appropriation ||
    'FROM pr.earnings where empnumber = ' || user_number || ') INTO ?';
PREPARE S2 from stmt2;
EXECUTE S2 using active_appropriation;

Best Answer

The problem with your query is that you need to construct meta-data (name of a column) with data. You therefore need to first construct the query, and then execute it using dynamic SQL. Something like (untested)

CREATE PROCEDURE P
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM ...;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ... ';
    EXECUTE IMMEDIATE STMT;
END @

I used @ as a statement terminator. Note that the compiler has no way to verify that the query makes sense, so you may end up with run-time errors.

Debugging stored procedures

IBM Datastudio has a fully-fledged stored procedure debugger, i.e. you can set breakpoints, inspect variables, etc. If you for some reason does not want to install it (or like me, can not find the download page:-), you can use the DBMS_OUTPUT module:

~]$ # add some sample data
~]$ db2 "create table t (APPROPRIATION char(1) not null)"
~]$ db2 "insert into t (APPROPRIATION) values ('1')"
~]$ db2 "insert into t (APPROPRIATION) values ('2')"
~]$ db2 "insert into t (APPROPRIATION) values ('3')"
~]$ db2 "insert into t (APPROPRIATION) values ('4')"

~]$ cat p.sql 
CREATE PROCEDURE P()
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM T ORDER BY RAND() FETCH FIRST 1 ROWS ONLY;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ...';
    call dbms_output.put_line('DEBUG: ' || STMT);
    -- EXECUTE IMMEDIATE STMT;
END @

~]$ db2 -td@ -f p.sql

~]$ db2 "SET SERVEROUTPUT ON"
DB20000I  The SET SERVEROUTPUT command completed successfully.
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP2 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP1 FROM ...
~]$ db2 "CALL P()"

  Return Status = 0

DEBUG: SELECT APPROP3 FROM ...

~]$ db2 "SET SERVEROUTPUT OFF"

Another option is to use an out variable:

~]$ cat p2.sql 
CREATE PROCEDURE P2(OUT outstmt VARCHAR(100))
LANGUAGE SQL
BEGIN
    DECLARE STMT VARCHAR(100);
    DECLARE APPROP CHAR(1);

    SELECT APPROPRIATION INTO APPROP FROM T ORDER BY RAND() FETCH FIRST 1 ROWS ONLY;
    SET STMT = 'SELECT APPROP' || APPROP || ' FROM ...';
    SET outstmt = stmt;
    call dbms_output.put_line('DEBUG: ' || STMT);
    -- EXECUTE IMMEDIATE STMT;
END @

~]$ db2 -td@ -f p2.sql

~]$ db2 "CALL P2(?)"

  Value of output parameters
  --------------------------
  Parameter Name  : OUTSTMT
  Parameter Value : SELECT APPROP1 FROM ...

  Return Status = 0

One major drawback with both these methods is that they will not produce any output when you need it the most, i.e. when your procedure fails.

For future posts, always include a reproducible setup. Either include DDL (create table statements, etc) and SQL (insert table statements) or use a service like DB<>Fiddle.

Most people (including myself) usually just skip to the next question unless there an easy way to reproduce the problem. Not only will you get more answers, but they will also be ready to use since you defined the setup. As of now, if my answer is at all usable for you, you will have to adapt it to your actual setup since I invented some imaginary tables etc.

Related Question