Re: Embedded SQL: 6 views of data, 6 identical cursors, excepting the Where clause

Booth

You can't do that directly. But you can put host variables in the SELECT statement, then use the USING clause in the FETCH statement - that would work if your WHERE clause uses the same columns.

It may also be possible to use a CASE in the SELECT statement to modify the WHERE clause. Something like this, I think - others can correct or extend this technique I found by googling for "where clause case"

SELECT * FROM ORDERS
WHERE
(
CASE
WHEN @VAR1 = 'Customers' AND CustomerID = @VAR2 THEN 1
WHEN @VAR1 = 'Employee' AND EmployeeID = @VAR2 THEN 1
ELSE 0
END
) = 1

As another hit mentioned, CASE is an expression and cannot be used to insert different code blocks..

Now the WHEN bits do not need to use the same variables - the body of each WHEN just needs to be an expression that evaluates to true or false.

I'm guessing on that usage but it seems feasible. And host variables can be used in there for the values, with actual values specified in a USING clause in the FETCH.

Now that is embedded SQL - another option might be to use Query Management queries - a QM query can have substitution variables that can be even an entire WHERE clause body, like this -

SELECT &FIELDS FROM &LIB/&FILE WHERE &CONDITIONS

The STRQMQRY command could be like this - assume name of query is GENSQL

STRQMQRY GENSQL SETVAR((FIELDS 'FIELD1, FIELD2') (LIB 'MYLIB') (FILE 'MYFILE') (CONDITIONS 'FIELD3 = 5'))

That becomes this statement -

SELECT FIELD1, FIELD2 FROM MYLIB/MYFILE WHERE FIELD3 = 5

The STRQMQRY has an output parameter, so you can point this SELECT to and *OUTFILE.

I don't know, just thinking weird SFW thoughts on a Friday morning!

HTH
Vern

A large file, with 6 views over the file. The requirement is to use SQL to get the same data, grouped and ordered in the same way, from the file excepting with a different Where clause. Moving the Where clause from the Declare Cursor to the Fetch Cursor would reduce a lot of redundant code. Is there a way to do that or similar?