Dynamic Lists In Static SQL Queries
March 31, 2015 Ted Holt
|
Hey, Ted: We have an SQL query that can take from one to 13 two-byte codes that become part of an IN clause for row selection. We have no idea which codes they will need to see beforehand; it’s an interactive thing. We are using dynamic SQL. I would like to find a static solution. Any suggestions? –Harold Sure, Harold. I do this sort of thing from time to time, and I use static SQL to do it. Let’s say those two-byte codes are state abbreviations, like the state abbreviations we use in the USA. You probably have a form or display into which the user keys the codes, but I’m going to use a command interface for an example. Here’s command AR102, which allows the entry of up to 12 state abbreviations.
CMD PROMPT('Print a Customer Report')
PARM KWD(STATE) TYPE(*CHAR) LEN(2) +
SNGVAL((*ALL 'XX')) MAX(12) EXPR(*YES) +
PROMPT('State abbreviation(s)')
We can run queries using commands like these: AR102 STATE(CA CO) AR102 STATE(TX MN VT) AR102 STATE(CA GA NM VT MN CO) This command runs RPG program AR102R, which uses the dynamic approach you’re using.
H dftactgrp(*no) actgrp('AR')
Far102p o e printer usropn
D AR102R pr extpgm('AR102R')
D inStateList 26a const
D AR102R pi
D inStateList 26a const
D String s 256a varying template
D cSqlEof c const('02000')
D Command s like(String)
D StateList s like(String)
D CustRec e ds extname('QCUSTCDT')
*inlr = *on;
BuildListOfStates ();
BuildCursor ();
BuildReport ();
return;
P BuildListOfStates...
P b
D Size ds 2
D ListSize 5i 0
D Offset s 3u 0
D Ndx s 3u 0
D Quote c const('''')
D Sep s 1a varying
Size = %subst(inStateList:1:2);
Offset = 1;
StateList = '(';
For Ndx = 1 to ListSize;
Offset += 2;
StateList = StateList + Sep + Quote
+ %subst(inStateList:Offset:2) + Quote;
Sep = ',';
endfor;
StateList = StateList + ')';
P e
P BuildCursor...
P b
Command = 'select * from qcustcdt ' +
' where state in ' + StateList +
' order by state, cusnum';
exec sql declare c1 cursor for p1;
exec sql prepare p1 from :Command;
P e
P BuildReport...
P b
exec sql open c1;
open ar102p;
dow '1';
exec sql fetch c1 into :CustRec;
if sqlstate >= cSqlEof;
leave;
endif;
write detail;
enddo;
close ar102p;
exec sql close c1;
P e
The command sends the list of codes to the RPG program as a string. The first two bytes are a signed integer that tells how many values there are in the list. The state abbreviations follow. The BuildListOfStates subprocedure converts the list into the form of an IN clause. If I run this command: AR102 STATE(CA CO MN) The RPG program receives this: **CACOMN The two asterisks are a lie. What’s really there is the integer 3 (hexadecimal 0003), which I have no way of accurately representing in text. The RPG program generates this:
('CA','CO','MN')
The entire SQL command looks like this:
select * from qcustcdt
where state in ('CA','CO','MN')
order by state, cusnum
AR102R reads file QCUSTCDT, which is in library QIWS, selecting the rows from the indicated states. This works fine. There’s nothing wrong with it. However, if you prefer static SQL, as I do, you can’t build the IN clause dynamically. Here’s a technique that works for me. First, I create a temporary table in which to load the codes. One code, one row. Then I refer to that table in the IN clause. Here’s the revised program.
H dftactgrp(*no) actgrp('AR')
Far102p o e printer usropn
D AR102R pr extpgm('AR102R')
D inStateList 26a const
D AR102R pi
D inStateList 26a const
D String s 256a varying template
D cSqlEof c const('02000')
D CustRec e ds extname('QCUSTCDT')
*inlr = *on;
BuildListOfStates ();
BuildCursor ();
BuildReport ();
return;
P BuildListOfStates...
P b
D Size ds 2
D ListSize 5i 0
D Offset s 3u 0
D Ndx s 3u 0
D State s 2a
exec sql declare global temporary table List
(State char(2))
with replace;
Size = %subst(inStateList:1:2);
Offset = 1;
For Ndx = 1 to ListSize;
Offset += 2;
State = %subst(inStateList:Offset:2);
exec sql insert into session.List values (:State);
endfor;
P e
P BuildCursor...
P b
exec sql declare c1 cursor for
select * from qcustcdt
where state in (select State from session.List)
order by state, cusnum;
P e
P BuildReport...
P b
exec sql open c1;
open ar102p;
dow '1';
exec sql fetch c1 into :CustRec;
if sqlstate >= cSqlEof;
leave;
endif;
write detail;
enddo;
close ar102p;
exec sql close c1;
P e
Notice how the BuildListOfStates and BuildCursor subprocedures have changed. BuildListOfStates creates temporary table LIST in QTEMP and writes each code into it. BuildCursor refers to LIST in the IN clause. For completeness, here’s the printer file definition. A REF(QCUSTCDT) A R DETAIL SPACEB(1) A CUSNUM R 1 A LSTNAM R +1 A INIT R +1 A STREET R +1 A CITY R +1 A STATE R +1 A BALDUE R +1EDTCDE(1)
|
