Can you use LAST in a WHERE clause?

I'm working on a routine to needs to retrofit a table to set values based on old records. I want to exclude lines with a set of timestamps that match a prior record's timestamps, and I just absent-mindedly coded this --

WHERE EFFECTIVE_END_DT NE LAST EFFECTIVE_START_DT;

I promptly discovered that it isn't enforced. At first I thought I was using it incorrectly but then it got me to thinking . . . how the heck would a you code a WHERE clause with a LAST in it? You can't determine the value of a prior record until you actually HAVE a prior record, and I'm thinking that I'm asking for something that just ain't gonna happen. Has anyone stumbled across this before?

J.

Posts: 1012 | Location: At the Mast | Registered: May 17, 2007

RSquared

Guru

posted

Hide Post

John,

I do believe that the table has to be sorted on the field(s) that you comparing in your LAST comparison, otherwise you don't know what you are going to get.

WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit

Posts: 398 | Registered: February 04, 2008

Ignored post by RSquared posted

Show Post

Guru

posted

Hide Post

You can do a compute and then a WHERE TOTAL (I am basically getting the first car from each country):

 
TABLE FILE CAR
PRINT 
     CAR.COMP.CAR
     COMPUTE new_country/A10 = IF LAST CAR.ORIGIN.COUNTRY EQ CAR.ORIGIN.COUNTRY THEN '' ELSE CAR.ORIGIN.COUNTRY; NOPRINT
WHERE TOTAL new_country NE '';
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

Without the where statement:
JAGUAR
JENSEN
TRIUMPH
DATSUN
TOYOTA
ALFA ROMEO
MASERATI
AUDI
BMW
PEUGEOT

With it:
JAGUAR
DATSUN
ALFA ROMEO
AUDI
PEUGEOT
[/code]

This message has been edited. Last edited by: J,

WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs

Posts: 402 | Location: Upland, IN | Registered: June 08, 2012

Ignored post by J posted

Show Post

Wep5622

Virtuoso

posted

Hide Post

You may be able to COMPUTE fields for comparison and then use WHERE TOTAL to do the actual comparison.

It's not going to be particularly efficient, as the filtering is only happening once the result is at the FOCUS side of things, but I think it would work.

COMPUTE LAST_EFF_START_DT/HDMYY = LAST EFFECTIVE_START_DT;

WHERE TOTAL LAST_EFF_START_DT NE EFFECTIVE_END_DT;

As said, you need to make sure your data is correctly ordered to get a correct result.

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010

Ignored post by Wep5622 posted

Show Post

j.gross

Virtuoso

posted

Hide Post

If your procedure will first sort the data by the date field, how do you know which to keep and which to discard (or doesn't that matter in your context)?

In any event, if WF will do the sorting, you can use WHERE TOTAL to trim it in the same TABLE request, a la

PRINT ...
BY EFFECTIVE_END_DT
[BY tie-breaker]
WHERE TOTAL EFFECTIVE_END_DT NE LAST EFFECTIVE_START_DT;

[edit:]
Then again, if your data is sourced in a rdbs, I think you can use

TABLEF ...
PRINT ...
BY EFFECTIVE_END_DT
[BY tie-breaker]
WHERE EFFECTIVE_END_DT NE LAST EFFECTIVE_START_DT;

WF should pass the sorting through to the data server, and can then discard the duplicates (w/r/t date), as it browses through the returned (sorted) answerset.

This message has been edited. Last edited by: j.gross,

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Show Post

Please Wait. Your request is being processed...