Jean,
That should fail. The column names would not be available to be used in the
SELECT portion of the clause. If you use the HAVING clause as part of the
join, the column names are available to be used.
Replace the Disponible field with this and see if it works:
CAST(EVALUATE("MIN(sal.capacite, sv.capacite, ta.capacite, ta.maxjour)") AS
Integer) AS disponible
Bill Anderson
On Tue, Oct 28, 2014 at 1:33 PM, Jean MAURICE <[email protected]>
wrote:
> 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/CA+O1enH=noa7tjshbhqwlh3p344mzkjftuscbwexavmwt3g...@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.
