SQL SELECT * FROM GGORDER WHERE ORDER_DATE BETWEEN '1996/01/01' AND '1996/02/01'; TABLE ON TABLE HOLD AS HOO1 END -RUN -SET &ORDER_DATE = ; TABLE FILE HOO1 BY HIGHEST 1 ORDER_DATE ON TABLE HOLD AS MAX_DATE END -RUN -READFILE MAX_DATE -RUN -TYPE ORDERDATE = &ORDER_DATE TABLE FILE HOO1 PRINT ORDER_DATE STORE_CODE ORDER_NUMBER QUANTITY ON TABLE SUBHEAD "Original booking date month-to-date through &ORDER_DATE" ON TABLE PCHOLD FORMAT HTML ON TABLE NOTOTAL ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET SQUEEZE ON ON TABLE SET EMPTYREPORT ON ON TABLE SET HTMLCSS ON ON TABLE SET HTMLENCODE ON ON TABLE SET CACHELINES 100 ON TABLE SET STYLE * ENDSTYLE END -RUN
quote:I am using a SQL query
quote:
What SQL engine are you using? Sql Server, Oracle, etc?
ON TABLE SUBHEAD "Original booking date month-to-date through <MAX.ORDER_DATE"
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |
quote:Originally posted by Tony A:
Any reason that you've decided the following would not suffice?ON TABLE SUBHEAD "Original booking date month-to-date through <MAX.ORDER_DATE"
T
quote:Originally posted by Mike Williams:
Not totally certain how to do this in one pass, but you could save your results in a hold file, then access that hold file to save your max value as a variable, then create your final report and place that variable in the header.
So a quasi example:SQL SELECT * FROM GGORDER WHERE ORDER_DATE BETWEEN '1996/01/01' AND '1996/02/01'; TABLE ON TABLE HOLD AS HOO1 END -RUN -SET &ORDER_DATE = ; TABLE FILE HOO1 BY HIGHEST 1 ORDER_DATE ON TABLE HOLD AS MAX_DATE END -RUN -READFILE MAX_DATE -RUN -TYPE ORDERDATE = &ORDER_DATE TABLE FILE HOO1 PRINT ORDER_DATE STORE_CODE ORDER_NUMBER QUANTITY ON TABLE SUBHEAD "Original booking date month-to-date through &ORDER_DATE" ON TABLE PCHOLD FORMAT HTML ON TABLE NOTOTAL ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET SQUEEZE ON ON TABLE SET EMPTYREPORT ON ON TABLE SET HTMLCSS ON ON TABLE SET HTMLENCODE ON ON TABLE SET CACHELINES 100 ON TABLE SET STYLE * ENDSTYLE END -RUN
Hope this helps.