This example of JavaScript addresses a long-standing question in QMF.
A user has a query Q2 that is parameterized with a substitution variable. At runtime the user does not know what value to supply. The value can only be attained by running another query Q1. So, the user runs Q1 to get the values, then runs Q2 and fills in the values from Q1 maybe running Q@ multiple times if there are multiple values returned from Q1. If the user could get the result of Q1 fed directly to Q2 the process could be automated.
Here is Q2, it is asking for a particular department number
Here is Q1, it gets the department number with the person having the smallest SALARY (of course it could be any other KPI)
Here is a JavaScript proc that solves the problem
The first line, proc.exec('RUN QUERY GETID_Q');, runs the query to get department number with the person with the smallest salary.
The second line creates a variable DEPTID to hold the value in row 1 and column 1 of the result set
var DEPTID = proc.getCellValue(1,1);
The third line feeds the variable DEPTID into the second query
proc.exec('RUN QUERY GETINFO_Q(&&DEPT=' + DEPTID );
The syntax of the third line is a bit tricky. It combines a QMF JavaScript function proc.exec with a pure QMF Proc command RUN QUERY GETINFO_Q(&&DEPT= with a pure JavaScript construct of the variable DEPTID.
Here are the text strings for these queries and procs if you would like to try them.
You need the QMF sample tables Q.STAFF and Q.ORG.
GETID_Q
SELECT DEPT FROM Q.STAFF
WHERE SALARY =(SELECT min(SALARY) FROM Q.STAFF)
GETINFO_Q
SELECT * FROM Q.ORG
WHERE DEPTNUMB=&DEPT
GETINFO_P
/*JavaScript*/
proc.exec('RUN QUERY GETID_Q');
var DEPTID = proc.getCellValue(1,1);
proc.exec('RUN QUERY GETINFO_Q(&&DEPT=' + DEPTID );
In the case above the value of the variable DEPTID is good only for the life of this proc. If you need it to be preserved for other procs or queries you can put it in the global parameter list with this
/*JavaScript*/
proc.exec('RUN QUERY GETID_Q');
var DEPTID = proc.getCellValue(1,1);
proc.exec('SET GLOBAL(DEPT='+DEPTID);
proc.exec('RUN QUERY GETINFO_Q');
Again the syntax of the third line combines a QMF JavaScript function proc.exec with a pure QMF Proc command SET GLOBAL (DEPT= with a pure JavaScript construct of the variable DEPTID.
Note how the pure QMF command is completely inside the single quotes and concatenated using a + with the pure JavaScript variable DEPTID. The complete string is then fed to the proc.exec.
In the example above, the first query was crafted to bring back a single cell. This made the getCellValue() easy. What if the first query had been like this:
SELECT * FROM Q.STAFF
ORDER BY SALARY DESC
Now the department number I need is in the third column of the last row.
This version of the proc will do the job.
/*JavaScript*/
proc.exec('RUN QUERY GETID3_Q');
var LASTROW =proc.getNumRows();
var DEPTID = proc.getCellValue(LASTROW,3);
proc.exec('SET GLOBAL(DEPT='+DEPTID);
proc.exec('RUN QUERY GETINFO_Q');
I know DEPT is column 3, the getNumRows() gets me the row number of the last row. Putting those facts together gives me the getCellValue(LASTROW, 3)