[Solved]How to create a temp file to be used as filter in SQL Where Clause?

How to create a temp file to be used as filter in SQL Where Clause?
In example bellow, I will retrieve the latest 5 dates from database table. Save it as a temp file. The Temp file will be used as a filter in next SQL WHRER clause, to print out employee whose ‘hiredate’ are in the first retrieved temp file.
Where EMPDATA.EMPDATA.HIREDATE IN the-temp-file.
I don’t know what format the save file should be, so it can be used as the input list ( or file) for the Where Clause. Also I got error when use SQL command‘ Where …. IN’ while read from a list or file. Thanks for your help in advance.

 
TABLE FILE EMPDATA
BY  HIGHEST EMPDATA.EMPDATA.HIREDATE
WHERE RECORDLIMIT EQ 5
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE SAVE AS 'LAST_5_DATES' FORMAT XML
END


TABLE FILE EMPDATA
PRINT 
     EMPDATA.EMPDATA.DIV
     EMPDATA.EMPDATA.LASTNAME
     EMPDATA.EMPDATA.FIRSTNAME
WHERE   EMPDATA.EMPDATA.HIREDATE IN  'LAST_5_DATES' ;
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

This message has been edited. Last edited by: Emily Lee,

WebFOCUS 8.2.01 AppStudio
HTML, PDF, Excel

Posts: 61 | Registered: March 12, 2008

Francis Mariani

Expert

posted

Hide Post

FORMAT ALPHA and WHERE IN FILE:

TABLE FILE EMPDATA
BY  HIGHEST HIREDATE
WHERE RECORDLIMIT EQ 5
ON TABLE SAVE AS LAST_5_DATES FORMAT ALPHA
END


TABLE FILE EMPDATA
PRINT 
DIV
LASTNAME
FIRSTNAME
WHERE HIREDATE IN FILE LAST_5_DATES ;
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = ENDEFLT,
$
ENDSTYLE
END

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Emily Lee

Gold member

posted

Hide Post

Hi Francis,

Thansk you very much. It works.
The UserForum is such as great community, with so many experts offering help in short time.

thanks again.

WebFOCUS 8.2.01 AppStudio
HTML, PDF, Excel

Posts: 61 | Registered: March 12, 2008

Ignored post by Emily Lee posted

Show Post

Please Wait. Your request is being processed...