Dynamic SQL using DB2

0
votes

I have been researching this for a few days now and can not specifically find an answer to my issue. I want to be able to dynamically create sql statements to run against my DB2 database. Below is some test code I have playing with to see how the DB2 interprets my sql, but I get an error every time I run this. Please help. 

Begin

Declare Monthcol integer;
Declare TXPage integer;
Declare TXYear integer;
Declare text varchar(2000);

set Monthcol = 11;
set TXPage = 10190;
set TXYear = 2018;

set text = 'Select GLMN'|| Monthcol ||  'from gldbfa.glpgl where glyear = 
2018 and glpage = 10190';
Print text;

end;

I have tried casting the variables to varchar and I have tried moving the print section to after the End. I get

"SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token TEXT was not valid. Valid tokens: :. Cause . . . . . : A syntax error was detected at token TEXT. Token TEXT is not a valid token."

if print is before end.

I get

"SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token PRINT was not valid. Valid tokens: ( CL END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER. Cause . . . . . : A syntax error was detected at token PRINT."

if print is after the End.

This is super easy to do in SQL Server, but I am very new to DB2. Thank you.

3
db2dynamic-sqldb2-400
Consider studing the Db2-documentation and the Db2-example programs when you are learning. If your code block is supposed to be an anonymous-block, then 'print' is not an SQL statement. Study the Db2-documentation for your platform (i-series) to learn about the EXECUTE IMMEDIATE statement, or alternatively the two statements for PREPARE and EXECUTE. - mao
Thank you. I tried using the execute immediate statement before but my syntax was wrong, so this was hopefully supposed to show me how DB2 was reading my statement. I will review further. - jrushing
You will also need to set the statement terminator to something other than ;. Are you using any particular tool to run your SQL statements? IBM Data Studio will syntax check locally, which can be a good help when struggling with syntax - Paul Vernon
Okay. I have just been using the run sql scripts from the iNavigator program. I will get the IBM Data Studio and try that to see if it will help with the syntax. - jrushing

3 Answers

0
votes

I know this may not be an ideal solution but one workaround would be to use case logic on all twelve rows as follows:

SELECT 
CASE
 WHEN :Monthcol = 1 THEN GLMN01 
 WHEN :Monthcol = 2 THEN GLMN02
 ---
 --- etc
 ---
 WHEN :Monthcol = 12 THEN GLMN12
END AS "GLMN"
FROM gldbfa.glpgl 
WHERE glyear = :TXYear AND glpage = :TXPage
0
votes

If the example code you posted is copy/pasted from your actual code then your problem is probably just the missing spaces in your concatenation.

Begin

Declare Monthcol integer;
Declare TXPage integer;
Declare TXYear integer;
Declare text varchar(2000);

set Monthcol = 11;
set TXPage = 10190;
set TXYear = 2018;

set text = 'Select GLMN' || Monthcol || ' from gldbfa.glpgl
            where glyear = 2018 and glpage = 10190'; <----- You were missing a space after Monthcol so your select statements was 'Select GLMN11from gldbfa...'

-- execute statment here    
end;
0
votes

Db2 for IBM i does not have a print

You need to be looking at the Db2 for IBM i docs, not Db2 for LUW.
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzahg/rzahgsql.htm

In answer to your comment, yes you can create dynamic queries...

Howver, doing so inside an SQL Stored procedure can be tricky...all depends on what you want to do with the results. See this question... Refer to table name dynamically in DB2/400 SQL query..?

What do you want to do with the row returned?