QMQRY is a stored string with markers in it that can be replaced; you can replace parts of tokens or a whole order by clause because you are just replacing the marker. It doesn't know sql until after all the strings are replaced; before then it is just string handling.
This is similar to running dynamic SQL imbedded in RPG. You compose the string with string handling, and then prepare it. Before the prepare, nothing actually knows that the string is going to be SQL, and how you compose the string is up to your creativity.
Host variables are no longer strings; they are areas of memory that can be pointed to by pointer and are accessed by bound routines that actually do SQL. The routines don't care what you named the memory; they just point to it. So they are only used for discrete data elements. You can't use one for something like an expression; you can use it to specify the data on which the expression works. You can't supply 2*3 as the host variable, but you can specify a separate host variable that contains 2 and another that contains 3. The SQL routines do not need to know the variables in advance since they are just going to do the same things to the information at that memory location; all this can happen after parsing.
Table names are more than data elements; they bring in lots of metadata when the SQL is being interpreted. So they are needed before you start parsing the SQL, and can't be supplied as a substitution variable. All the SQL keywords (select, from, where, order by) also need to be there before the parsing. However, you can supply them as variables in QMQRY during the string handling phase. If you try CLI, you very much notice what is supplied by pointer to memory and what is supplied as an SQL string.
Sent from Outlook<
http://aka.ms/weboutlook>
________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> on behalf of Brian Parkins <goodprophet.bp@xxxxxxxxx>
Sent: Tuesday, February 20, 2018 4:07 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Converting QM Query to rpg embedded SQL
<phew!>
To my untrained eye, I don't believe you can use Host Variables on a
CREATE TABLE statement. (There doesn't appear to be a definitive
reference of where you CAN use HV's. The manual simply states they are
"commonly used" on WHERE, INTO, SELECT, SET, VALUES and CALL.)
Perhaps one of resident experts could add some insight?
If this is a showstopper, then you'll need to consider using Dynamic SQL
embedded in your RPG IV code.
Brian.
midrange.com
