Hi Bill,
it failed !
here is a small SQL query :
SELECT DISTINCT ta.codtarif AS tarif, ;
sv.clef AS clefserv, sv.salle, sv.capacite AS servdispo, sv.libelle AS
service, ;
sal.capacite AS salledispo, sal.libelle AS nomsalle, ;
ta.capacite AS maxtarif, ta.maxjour AS maxjour, ta.libelle AS libeltarif, ;
CAST(EVALUATE("MIN(servdispo, salledispo, maxtarif, maxjour)") AS
Integer) AS disponible ;
FROM cantine_services sv ;
INNER JOIN cantine_salles sal ON sv.salle = sal.clef, ;
tarifs_cantine ta ;
WHERE ta.anscol = "W" INTO CURSOR dispo READWRITE
in fact, inside the EVALUATE, the columns servdispo, salledispo, ... are unknown
as are sv.capacite, sal.capacite ...
The foxil
Le 28/10/2014 21:12, Bill Anderson a écrit :
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/[email protected]
** 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.
