Dan,
You cannot combine a column reference and an aggregate column reference
like that in a WHERE clause. You have to separate them into the main query
and the subquery. The subquery finds the max effdate, and hands it to the
main query.
Is this in a program, or in a form or report expression? If a program, use
the less obsolete SELECT syntax, instead of the SET VAR syntax:
SELECT id INTO vid vnullindicator FROM pvpn +
WHERE partnumber = .vpartnumber +
AND effdate = +
(SELECT MAX (effdate) FROM pvpn WHERE partnumber = .vpartnumber AND effdate
< .dateeff)
If it's a form or report expression, same idea:
VID= id IN pvpn WHERE partnumber = .vpartnumber AND effdate = (SELECT MAX
(effdate) FROM pvpn WHERE partnumber = .vpartnumber AND effdate < .dateeff)
Bill
On Thu, Oct 9, 2014 at 1:27 PM, Dan Goldberg <[email protected]> wrote:
> This for some reason is not working for me and I am having a mind block.
>
> What I want to get the id of a row that is the most recent that is less
> than vdateeff
>
> I tried:
>
> Set var vid= id in pvpn where partnumber = .vpartnumber and max(effdate) <
> .dateeff
>
> It gives me an error "illegal select function"
>
> I am probably missing something stupid. Anybody have any idea on what I am
> doing wrong?
>
> TIA
>
> Dan Goldberg
>
>
>
