>I want to use the VFP min function in a sql statement, but it gives me an error, because the sql min function doesn't work the same. In this example, the second one works, but I am wondering if there is a faster way.
>
>create table foo ( iFid1 i, iFid2 i )
>insert into foo values (-1,-1)
>insert into foo values (-1,1)
>insert into foo values (1,-1)
>insert into foo values (1,0)
>insert into foo values (1,1)
>insert into foo values (0,0)
>
>select min( 0, iFid1 ) as iMin1, min( 0, iFid2) as iMin2 ;
>from foo ;
>having min( iMin, iMin2 ) < 0
>
>select iif( ifid1<0, ifid1, 0 ) as iMin1, iif( iFid2<0, iFid2, 0 ) as iMin2 ;
>from foo ;
>having iMin1 < 0 or iMin2 < 0
You could make your own MIN type function, but I don't know if that's any faster/better/more clear than the IIF.
mymin.prg
LPARAMETERS pn1,pn2
IF pn1 RETURN pn1
ELSE
RETURN pn2
ENDIF
SELECT mymin(0,ifid1) AS imin1...