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.

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.

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,

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.

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,