Jean, First, this... (VFP 9 specific)
USE ADDBS(HOME(1)) + [Browser.DBF] SHARED NOUPDATE SELECT CAST(EVALUATE([MIN(1, 2, 3, 4)]) AS N(7, 2)) AS MinimumTest FROM Browser INTO CURSOR Test That shows how to use the VFP MIN() function in a SQL Select while formatting the MinimumTest field. So in theory, replace those 4 numbers with your calculations and you're all set. But be careful of NULLs... I'd think about wrapping these 4 numbers with EVL(< subquery >, 0) or even EVL(< subquery >, 10**10) depending on what you want to do with the result when (if?) the subquery returns a NULL. Bill Anderson On Tue, Oct 28, 2014 at 12:22 PM, Paul Hill <[email protected]> wrote: > On 28 October 2014 17:17, Jean MAURICE <[email protected]> wrote: > > I have a curious issue ! > > > > In a SQL query, I get 4 numeric fields from 4 tables (with subqueries). I > > want a fifth field containing the minimum value of the 4 fields (of the > > current line). But when I write MIN(), even in an EVALUATE command, SQL > > thinks it is a SQL MIN(). > > > > Is there a way to compute this minimum WITHOUT creating an external > function > > ? > > Could you use the ICASE function? > > SELECT amount = ICASE( ; > f1>f2 AND f1>f3 AND f1>f4, f1, ; > f2>f3 AND f2>f4 AND f2>f1, f2, ; > f3>f4 AND f3>f1 AND f3>f2, f3, ; > f4>f1 AND f4>f2 AND f4>f3, f4) ; > FROM results > > Ugh! > > In SQL Server you could do: > > SELECT MAX(amount) FROM > ( > SELECT amount = MAX(amount) FROM table1 > UNION ALL > SELECT amount = MAX(amount) FROM table2 > UNION ALL > SELECT amount = MAX(amount) FROM table3 > UNION ALL > SELECT amount = MAX(amount) FROM table4 > ) AS results > > Don't think VFP supports this syntax. > Dammit! Stephen just posted the same thing :-) > > -- > Paul > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CA+O1enGm3BOBcLao6StDde7Ez3hoFj1hs68p+UAAtWO=+mu...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
