DB2 query manager for i5
Hal
I have this long SQL statement I wrote last week that basically
calculates class ranks for a school. Right now it is hard-coded to do
just seniors at one high school. By changing the grade in 3 or 4
places in the query, I can do any grade from 09 thru 12. The same goes
for using the query for a different school.
We have 5 high schools, and they all have 4 grades, so I need a way to
make that grade and school into a variable that I can pass into the
SQL statement at execute time. My first thought was to use the DB2
query manager.
So I converted the SQL statement into a query manager query...and it
won't run. So I pull it up in the query editor, hit F4 to prompt on
the statement, and I get this error : " Token ; was not valid. Valid
tokens: <END-OF-STATEMENT>. "
Granted, this thing runs just fine with the RUNSQLSTM command. The ;
being referenced is at the end of a sub-select as part of the where
clause.
So...for those more knowledgeable than me....does the query manager
not support nested queries/sub-selects? If not, is there another
approach to putting variables into an otherwise functional SQL
statement?
Thanks,
Chris
CRPence
The QM Query supports a single statement compiled into the object
type *QMQRY via CRTQMQRY. The single statement can support nested
queries\sub-selects. However, there is no semicolon in a single
statement. For multiple interpreted statements, separated by a
semicolon, the RUNSQLSTM is a statement processor. Thus if the
statement is running properly in the RUNSQLSTM with a semicolon, it
is presumably being treated as two consecutive statements.?
FWiW if there are five entities, consider that the definition of
the query for each entity can be encapsulated in its own VIEW; e.g.
CREATE VIEW HS# as (SELECT ... FROM HSmaster WHERE HSname = 'xxHS')
and repeat for each HS# one to five. That establishes the option of
OVRDBF HSmaster TOFILE(HS1) OVRSCOPE(*CALLLVL), for example, to
enable the QMQuery to obtain the results from HS1 without any
variable. Not to discourage variables... just pointing that out
since often such divisions are both desirable and worthwhile.
For SQL I often used REXX, there is also the DB2 command line
available from within QSH, and of course there is compiled programs
with host variables or parameter markers in [extended] dynamic SQL.
Within iNav database Run SQL suppport, I seem to recall that for a
statement with a properly formed use of a parameter marker [i.e.
unlike a variable in a QMQRY where the variable can even replace
part of the SQL statement versus literals], parameter marker will be
prompted. The DB2 Web Query for i enables variables and even
pull-downs via a [web] browser interface.
Regards, Chuck
Hal
> type *QMQRY via CRTQMQRY. The single statement can support nested
> queries\sub-selects. However, there is no semicolon in a single
> statement. For multiple interpreted statements, separated by a
> semicolon, the RUNSQLSTM is a statement processor. Thus if the
> statement is running properly in the RUNSQLSTM with a semicolon, it
> is presumably being treated as two consecutive statements.?
>
You are absolutely correct. The SQL statement is infact two separate
statements. One calculates unweighted rank, the other does weighted
rank. Both DBVisualizer and RUNSQLSTM seem to do some kind pre-parse
that separates the statements so each runs each separately. I
suspected that the query manager did not support this, but a quick
look through some websites on the topic didn't give me confirmation.
Anwyay, I took out the second part of the DB2 query manager query so
that my *QMQRY object is only one statement, and it ran on the first
try.
So I will play around with it some more. Thank you for the point in
the right direction.
Regards,
Chris
helmuterik
Might also add that a stored procedure might be an alternative for
variable based processing. Of course, it's usually a bit more work
than an ad hoc query but I'll mention it anyway.
If you feel a need to break up the query in two statements, a common
table expression (CTE) can be an alternative to doing so.