Can I use a variable member name for CREATE ALIAS in a QMQRY?

[ukpi1b]

Short answer to your question is 'Yes'. You can use variables instead of libraries, files, aliases.... I think, your problem is setting variables in CL - quotes to be more specific. In your case &MBRX should look like 'ABCD' - no extra quotes required. Not sure about other variables. Also, all variables for SETVAR must be character. Provide more information about other variables and the answer will be more detailed. Hope this helps.

[stevejohnson]

Here is an example of what I have done to handle the quotes for the SETVAR values, and it works... DCL VAR(&QUOTE1) TYPE(*CHAR) LEN(1) VALUE('''') CHGVAR VAR(&PAYDATEV) VALUE(&QUOTE1|<&PAYDATEA|<&QUOTE1) You indicated that I only need single quotes for the &MBRX variable, but my attempts to create the variable surounded with single quotes has failed. I'd appreciate hearing your advice on this. Thanks! Steve

[ukpi1b]

There are at least 2 possible scenarios in CL program in your case. I will use simplified query and CL programs The same query ALQ is used in both cases: CREATE ALIAS QTEMP/SCJVMBR FOR &SRC Extracts from CL are shown below

---

Code

[stevejohnson]

ukpi1b, Thanks for your replies to my questions. In your example you used the structure 'YourLib/YourFile', but you didn't specify a member name. I believe that the required stucture for specifing a member name in QMQRY to be... CREATE ALIAS aliasname FOR libraryname/filename(membername). I have omitted the library name from my interactive SQL statement attempts at this and it works fine as... CREATE ALIAS aliasname FOR filename(membername) Using that as a starting point along with your suggestions, here are my latest three attempts from QMQRY... In the CLP... DCL VAR(&MBRX) TYPE(*CHAR) LEN(20) VALUE('TESTP(TSTMBR)') In the QMQRY... CREATE ALIAS SCJVMBR FOR &MBR Version 2 in the CLP... DCL VAR(&MBRX) TYPE(*CHAR) LEN(20) VALUE('TSTMBR') In the QMQRY... CREATE ALIAS SCJVMBR FOR TESTP(&MBR) Version 3 in the CLP... DCL VAR(&MBRX) TYPE(*CHAR) LEN(20) VALUE('(TSTMBR)') In the QMQRY... CREATE ALIAS SCJVMBR FOR TESTP&MBR Should all three of these variations be valid? The error message for all three variations is now... RUN QUERY command failed with SQLCODE -199. Keyword INSERT not expected. Valid tokens: . The statement after the CREATE ALIAS is... INSERT INTO SCJVMBR SELECT * and so on... In the IBM SLQ manual regarding the CREATE ALIAS statement I read... "If a member is specified, you can only use the alias in data manipulation (DML) SQL statements." I'm not sure what this statement should mean to me, but I think my use of it is ok since I don't have difficulty using the same statements in interactive SQL sessions without the variables. Thanks for any further assistance that you can provide on this! Steve

[stevejohnson]

Can I use a variable member name for CREATE ALIAS in a QMQRY? If not, why not?!? Here is the start of the QM query... CREATE ALIAS SCJVMBR FOR TESTP(&MBRX) INSERT INTO SCJVMBR SELECT * ...etc... In the calling CLP here is the command that I am trying to add the MBRX variable to for this purpose. The other setvar parms work fine, so I know that all of these character values are embedded properly within the required series of quotes... STRQMQRY QMQRY(TESTQMQRY) SETVAR((DPTBEGIN + &DPTBEGINV) (DPTEND &DPTENDV) (PAYDATE + &PAYDATEV) (MBRX &MBRV)) On execution, the process ends with a SQLCODE -104 error, pointing to a syntax issue with the MBRX variable. In the mean time I'll resort to doing an OVRDBF to that member using the variable in my CLP. Thanks in advance for any responses! Steve

[stevejohnson]

FWIW... I just wanted to post my findings here in the event that someone else can benefit from the info... With assistance from ukpi1b, I learned that I cannot enter multiple statements into a single QMQRY statement the way that you can with RUNSQLSTM. That was the problem that I was running into when I got the message "RUN QUERY command failed with SQLCODE -199. Keyword INSERT not expected. Valid tokens: .", which occurred when QMQRY tried to run the INSERT statement after running the CREATE ALIAS statement within the same QMQRY. I had tried several common end-of-statement characters with no success (ie. ; and ,). I can successfully run each statement from the CL using two STRQMQRY statements. It is too bad that QM queries cannot currently run multiple statements in the same QMQRY source. I think that this would be a great future enhancement. I hope that someone who can make it happen is listening! Steve