Re: The SQL Except statement not supported ?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Cindy Winegarden (cindy.winegarden_at_mvps.org)
Date: 08/15/04

  • Next message: Cindy Winegarden: "Re: Unique Records in table" 
    Date: Sun, 15 Aug 2004 15:47:46 -0400

    Hi Peter,

    EXCEPT is not supported in VFP's SQL engine.

    Which version of Visual FoxPro are you using? You have fields in your Select
    clause that are not aggregates and not in your Group By clause. These fields
    are basically meaningless.

    I don't understand your query - the two parts are exactly the same except
    the

    prest.prestfdate <<some relationship>> {d '2004-01-01'}

    statement where one is >= and one is <. I'm not familiar with the Except
    statement, but with the two instances of your date comparison your sets are
    mutually exclusive.

    Can you say in words the result set you are trying to get? Maybe there is
    another way to write your query. 

    -- 
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org  www.cindywinegarden.com
"Peter Depuydt" <Peter Depuydt@discussions.microsoft.com> wrote in message 
news:1BE8A5B1-08AA-4F5D-96EC-3A7A308D3847@microsoft.com...
> Hi folks,
>
> The following statement doesn't work. Apparently it is the "except"
> statement that is not understood. However if I run the 2 select statements
> separatly it works, allas it only gives the intermediat results.
>
> Does anyone have an idea ?
>
> Kind regards
>
> Peter Depuydt
>
> SELECT inter.u_agenceid, inter.name,inter.admstreet, inter.admzip,
> inter.admcity, inter.natregnr, Sum(prest.dnorinv)
> FROM prest, inter
> WHERE (prest.prestfdate>={d '2004-01-01'})  AND prest.interid =
> inter.interid  GROUP BY inter.natregnr
> HAVING sum(prest.dnorinv) > 29
>
> EXCEPT
>
> SELECT inter.u_agenceid, inter.name,inter.admstreet, inter.admzip,
> inter.admcity, inter.natregnr, Sum(prest.dnorinv)
> FROM prest, inter
> WHERE (prest.prestfdate<{d '2004-01-01'})  AND prest.interid = 
> inter.interid
> GROUP BY inter.natregnr
> HAVING sum(prest.dnorinv) > 29
> ;
  • Next message: Cindy Winegarden: "Re: Unique Records in table"