Stupid Parameter Tricks

Hey, Readers:

Parameter markers allow you to bind replacement data to a statement at runtime, and they can be quite useful in making statement execution plans reusable . In the April 26 Guru , we were dealing with a table of sales information and wanted a query that would show sales, gross profit, and gross profit percent against last year sales for the same time period. The query also features a calculation of the percent change between last year and this year’s gross profit. The problem with the query is that it had the values of the month and year hard coded into the SQL statement. This is bad form, as you have to dynamically build the SQL statement string in memory to squash the values for the year and month into the statement. Also, this statement could be considered dynamic SQL by the AS/400 and cause the query optimizer to re-optimize the SQL statement each time it sees the statement. To take advantage of packaged queries, we need to use parameter markers in the statement. In addition, we can make the query easier to use in code by implementing parameter markers. The question then becomes, “How can you use a parameter marker in the case statement?”

First, in order to try the following examples you will need to use an environment that supports parameter markers in SQL statements. The Run SQL Scripts in Client Access Express option cannot do this, nor can the Start SQL (STRSQL) command on the AS/400. However, you can download my freeware version of SQLThing from www.sqlthing.com in order to test statements that use parameter markers. The freeware version of SQLThing uses ODBC to connect to the AS/400 and allows you to use a graphical editor to test and develop SQL statements. More importantly, if a statement uses parameter markers, SQLThing will prompt you for the parameters and bind them to the statement, thus allowing you to test SQL statements exactly as they will be used in your application. If you do not use SQLThing to execute the following statements, you will need to write a program in VBScript, embedded SQL, or Java in order to use parameter markers and test these techniques.

If you use SQLThing, pull down the Environment menu and select Options Panel. Change the Isolation level to Read Uncommitted (*CHG) and check the Autocommit option. You are going to build non-journaled tables in QTEMP.

Execute the following create table statement and the inserts that follow it to create the sample data and insert records:

CREATE TABLE QTEMP.VENDSALES
	(VENDOR CHAR(10) NOT NULL,
	 YR INTEGER NOT NULL, 
	 MO INTEGER NOT NULL,
	 COST DECIMAL(10,2) NOT NULL,
	 SELLCOST DECIMAL(10,2) NOT NULL);
	 
INSERT INTO QTEMP.VENDSALES VALUES 

('ACME',2002,1,2340,2100);
INSERT INTO QTEMP.VENDSALES VALUES 

('ACME',2002,2,1023,900);
INSERT INTO QTEMP.VENDSALES VALUES 

('ACME',2002,3,4321,3680);
INSERT INTO QTEMP.VENDSALES VALUES 

('ACME',2002,4,2256,2199);
INSERT INTO QTEMP.VENDSALES VALUES 

('ACME',2001,1,1994,2122);
INSERT INTO QTEMP.VENDSALES VALUES 

('ACME',2001,2,2700,2411);
INSERT INTO QTEMP.VENDSALES VALUES 

('ACME',2001,3,1921,1811);
INSERT INTO QTEMP.VENDSALES VALUES 

('ACME',2001,4,1400,1422);
INSERT INTO QTEMP.VENDSALES VALUES 

('BROWN',2002,1,3654,2587);
INSERT INTO QTEMP.VENDSALES VALUES 

('BROWN',2002,2,1421,1210);
INSERT INTO QTEMP.VENDSALES VALUES 

('BROWN',2002,4,6545,5645);
INSERT INTO QTEMP.VENDSALES VALUES 

('BROWN',2001,1,9841,8754);
INSERT INTO QTEMP.VENDSALES VALUES 

('BROWN',2001,3,5689,5120);
INSERT INTO QTEMP.VENDSALES VALUES 

('BROWN',2001,4,5654,3987);

Now that you have the sample data, I will show how to use parameter markers in the select portion of your SQL statements. The AS/400 documentation doesn’t illuminate this very well, but you can use parameter markers in the SELECT portion of an SQL statement as long as the query parser understands the data type of the parameter that you will be passing. Try the following statement:

SELECT VENDOR,
	CASE WHEN ?=yr
		THEN cost ELSE 0 END
		FROM VENDSALES ;

Executing the statement will cause the AS/400 to return the error message, "SQL0418 User of parameter marker not valid.

Now, if you're running V5, try this:

SELECT VENDOR,
	DOUBLE(CASE WHEN ?=yr
		THEN cost ELSE 0 END)
		FROM qtemp.VENDSALES  ;

If you're at V4R5 or before, use this statement instead:

SELECT VENDOR,
	CASE WHEN CAST(? AS INTEGER)=yr
		THEN cost ELSE 0 END
		FROM qtemp.VENDSALES  ;

The AS/400 should cheerfully prepare the above statement. If you are using SQLThing and supply the parameter 2002, you will get the following results:

Vendor	Expression
ACME	2340
ACME	1023
ACME	4321
ACME	2256
ACME	0
ACME	0
ACME	0
ACME	0
BROWN	3654
BROWN	1421
BROWN	6545
BROWN	0
BROWN	0
BROWN	0

I have found that the AS/400 is pretty consistent in its support of parameter markers in the select portion of a query as long as you place a CAST or other data conversion-function around the marker so that it can interpret the data type. So, taking this one step further, we can rework the example query from last week to support any range of dates by adding parameter markers to the query:

SELECT VENDOR,
   SUM(ytd) AS YTD,SUM(ytdgp) AS YTDGP,
   (CASE WHEN SUM(ytd)>0 THEN SUM(ytdgp)/SUM(ytd)*100 ELSE 

0 END) AS YTDGPP,
   SUM(PYYTD) AS PYYTD, 
   SUM(PYYTDGP) AS PYYTDGP,
   (CASE WHEN SUM(pyytd)>0 THEN SUM(pyytdgp)/SUM(pyytd)*100 
		ELSE 0 END) AS pyYTDGPP,
(CASE WHEN SUM(ytdgp)>0 THEN 

(SUM(ytdgp)-SUM(pyytdgp))/SUM(ytdgp)*100 
		ELSE 0 END) AS YTDPCTCHG
 FROM 
(SELECT VENDOR,
	DOUBLE(CASE WHEN CAST(? AS 

INTEGER)=yr 
		THEN cost ELSE 0 END) AS YTD,
	DOUBLE(CASE WHEN CAST(? AS INTEGER) 

=yr 
		THEN cost ELSE 0 END) AS PYYTD,
      DOUBLE(CASE WHEN CAST(? AS INTEGER) 

=yr 
		THEN COST-sellcost ELSE 0 END) AS YTDGP,
	DOUBLE(CASE WHEN CAST(? AS INTEGER) 

=yr THEN COST-sellcost ELSE 0 END) AS PYYTDGP
		FROM QTEMP.VENDSALES  
	WHERE ((mo<=? AND yr=?) OR (mo<=? AND 

yr=?))) AS SALESRAW 
GROUP by VENDOR ORDER BY 1;

Executing the above query and passing the parameter data 2002, 2001, 2002, 2001,3, 2002, 3, 2001 will yield the following results:

VENDOR	YTD	YTDGP	YTDGPP	PYYTD	PYYTDGP	PYYTDGPP	YTDPCTCHG
ACME	7684.00	1004.00	13.07	6615.00	271.00	4.10	73.01
BROWN	5075.00	1278.00	25.18	15530.00	1656.00	10.66	-29.58

Dim Cmd1 as new ADODB.Command
Cmd1.ActiveConnection = Con1
Cmd1.CommandText = STMT
Cmd1.Parameters.Refresh
Cmd1.Parameters(0).Value = 2002 
Cmd1.Parameters(1).Value = 2001
Cmd1.Parameters(2).Value = 2002
Cmd1.Parameters(3).Value = 2002
Cmd1.Parameters(4).Value = 3
Cmd1.Parameters(5).Value = 2002
Cmd1.Parameters(6).Value = 3
Cmd1.Parameters(7).Value = 2001
Dim RS as new ADODB.Recordset
RS.CacheSize = 10
RS.Open Cmd1

The Parameters collections Refresh method causes the collection of parameter objects to be instantiated. Next, I fill in each parameter with the value that I want it to have. Finally, I create a recordset object, set its cachesize property, and then open the recordset using the command object.

Parameter markers are a cool thing to have in your arsenal of programming tricks. The ability to use parameter markers in the select clause can be used in a lot of situations other than this one. For example, you could set up a case statement to range continuous data into groups. By using parameter markers in the case statement, you could dynamically set the range of your groups and still avoid the pitfalls of dynamic SQL. Play with the technique and send me some examples of how you put parameter markers into practice.

-- Howard