User query results in another query

[dabrus]

I would like to use the results of one query in another query. How could I complete this in WF7?

Example:

SQL DB2 SET SERVER DB2_SERVER
SQL DB2
Select USERNAME from TST.USERS for read only with ur
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT
*
ON TABLE HOLD AS DATA1
END
-RUN

SQL DB2 SET SERVER DB2_SERVER
SQL DB2
Select USERNAME, LOGON_FAILURE_TMSTMP from TST.FAILED_LOGONS
WHERE USERNAME IN
(
-INCLUDE DATA1
)
FOR READ ONLY WITH UR
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT *
ON TABLE PCHOLD AS DATA2 FORMAT HTML
END
-RUN

 

[focusdev]

If you create a master for FAILED_LOGONS, then you can join the results back to the db2 table. Otherwise, you could probably make the data1 file comma delimited for the in statement, by using ON TABLE HOLD AS DATA1 FORMAT COM.

SQL DB2 SET SERVER DB2_SERVER
SQL DB2
Select USERNAME from TST.USERS for read only with ur
;
TABLE
ON TABLE HOLD AS DATA1
END
-RUN

TABLE FILE FAILED_LOGONS
PRINT
  USERNAME
  LOGON_FAILURE_TMSTMP
IF USERNAME EQ (DATA1)
ON TABLE PCHOLD AS DATA2 FORMAT HTML
END