Exclude deleted record with SELECT - SQL... 
Author Message
 Exclude deleted record with SELECT - SQL...

Hi all,
As we all know, SELECT - SQL statement doesn't regard deletion mark on any
records. Thus, if mytable has 10 deleted records, ones need to specify
SELECT myfield1, myfield2, ... FROM mytable WHERE !DELETED() in order to
retrieve just the valid (undeleted) record.

Q: Is there any other way to retrieve the valid record using SELECT - SQL
statement? It's very troublesome to always include the !DELETED() in every
query in the program.

TIA
Willianto



Wed, 07 Apr 2004 22:50:11 GMT  
 Exclude deleted record with SELECT - SQL...
SELECT-SQL does respect the setting of SET DELETED, so if you're using a
default data session, setting DELETED ON at program startup will filter out
any deleted records from your result sets. If you are using private data
sessions, you will need to issue this command for each data session. Using
the DELETED() function works for single table queries, but is otherwise
unreliable.

Best wishes,

John


Quote:
> Hi all,
> As we all know, SELECT - SQL statement doesn't regard deletion mark on any
> records. Thus, if mytable has 10 deleted records, ones need to specify
> SELECT myfield1, myfield2, ... FROM mytable WHERE !DELETED() in order to
> retrieve just the valid (undeleted) record.

> Q: Is there any other way to retrieve the valid record using SELECT - SQL
> statement? It's very troublesome to always include the !DELETED() in every
> query in the program.

> TIA
> Willianto



Thu, 08 Apr 2004 02:04:06 GMT  
 Exclude deleted record with SELECT - SQL...
Fox will evaluate the expression for whatever record is current when the SQL
command is issued.

To simplify, assume that we're on the first record in Foo.dbf; no records
are deleted.  Issuing:

    SET DELETED OFF
    SELECT * FROM Foo WHERE NOT DELETED('Foo')

will select all records, as expected.  Now, try the following:

    SELECT Foo
    GO TOP
    DELETE    && do not move the record pointer
    SELECT * FROM Foo WHERE NOT DELETED('Foo')

Result?  No records, because NOT DELETED('Foo') evaluates to .F. when the
command executes.  Continue with:

    SELECT Foo
    SKIP
    SELECT * FROM Foo WHERE NOT DELETED('Foo')

Since NOT DELETED('Foo') currently evaluates to .T., /all/ the records are
selected - even the deleted ones.  (Setting DELETED to ON will block those).

No, I can't explain the logic.

 - Rush


Quote:
> What happens if you went:

> ... !DELETED('table1') AND !DELETED('table2')

> would that make it more reliable for multiple tables, if generate somewhat
> longer select stataments?

> Damien

> > On Sat, 20 Oct 2001 19:04:06 +0100, John Holubiak wrote
> > SELECT-SQL does respect the setting of SET DELETED, so if you're using a
> > default data session, setting DELETED ON at program startup will filter
out
> > any deleted records from your result sets. If you are using private data
> > sessions, you will need to issue this command for each data session.
Using
> > the DELETED() function works for single table queries, but is otherwise
> > unreliable.

> > Best wishes,

> > John



> >> Hi all,
> >> As we all know, SELECT - SQL statement doesn't regard deletion mark on
any
> >> records. Thus, if mytable has 10 deleted records, ones need to specify
> >> SELECT myfield1, myfield2, ... FROM mytable WHERE !DELETED() in order
to
> >> retrieve just the valid (undeleted) record.

> >> Q: Is there any other way to retrieve the valid record using SELECT -
SQL
> >> statement? It's very troublesome to always include the !DELETED() in
every
> >> query in the program.

> >> TIA
> >> Willianto



Fri, 09 Apr 2004 05:56:37 GMT  
 Exclude deleted record with SELECT - SQL...
I hadn't seen this behavior using DELETED("tablename"), but testing your
code confirmed it. However, issuing

SELECT Foo
GO TOP
DELETE    && do not move the record pointer
SELECT * FROM Foo WHERE NOT DELETED()    && Omit the tablename

returned the non-deleted records.

Best wishes,
John


Quote:
> Fox will evaluate the expression for whatever record is current when the
SQL
> command is issued.

> To simplify, assume that we're on the first record in Foo.dbf; no records
> are deleted.  Issuing:

>     SET DELETED OFF
>     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> will select all records, as expected.  Now, try the following:

>     SELECT Foo
>     GO TOP
>     DELETE    && do not move the record pointer
>     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> Result?  No records, because NOT DELETED('Foo') evaluates to .F. when the
> command executes.  Continue with:

>     SELECT Foo
>     SKIP
>     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> Since NOT DELETED('Foo') currently evaluates to .T., /all/ the records are
> selected - even the deleted ones.  (Setting DELETED to ON will block
those).

> No, I can't explain the logic.

>  - Rush



Fri, 09 Apr 2004 13:13:57 GMT  
 Exclude deleted record with SELECT - SQL...
Hello.

There is always a profit  logical scheme behind MS brains  :o))

Fox was designed to be a speedy product.

So the select statement do first a meta-evaluation of conditions, just to know if it could be optimized or if to use indexes,
calculate the width of result columns, and so on. As you dont leave blank the alias in your deleted() condition, Fox thinks it
is a private function, not referred to a field nor to the current record, and then ... how to know the type and length of the
result ? by evaluating it, of course, and then keep the result for the rest of execution (like in a 'for' statement, where the
'from' and 'to' clauses are evaluated only one time, at the beginnig of the loop)

Two more samples of this logic :

1) run this :

    ************
    create myfile (myfield m)
    insert into myfile values ('hello')
    insert into myfile values ('hello boys')
    insert into myfile values ('hello boys, and smacks to girls')
    select rtrim(myfield) from myfile
    **************

the result is ...  hello, hello and hello !!!
because rtrim() is not a field reference, but a function evaluation, fox first evaluates the condition, current-line based,
finds 5 characters, and applies it to the whole result.
* the good way to do this is
    select max(len(rtrim(myfield))) from myfile into array xx     && because sql-max function applies on the whole file
    select padr(myfield,xx) from myfile

2) a beautiful other one : we want to make an auto-increment in an field

with the same file .. but put it in a program, cause there's a function call

    ********
    private cpt
    m.cpt = 0
    select padr(myfield,31) , incr_num() from myfile

    function incr_num
    *    generates unique number for a record
    m.cpt  = m.cpt +1
    return m.cpt
    ***********

and the numbering begins to ... 2 instead of 1, because Fox has evaluated the function incr_num to know what type of result the
function returns, and so have yet increased cpt variable before starting real work !

So, be prudent with functions ...

Grard from France.
- Open one's mind to open world mind -


Quote:
> Fox will evaluate the expression for whatever record is current when the SQL
> command is issued.

> To simplify, assume that we're on the first record in Foo.dbf; no records
> are deleted.  Issuing:

>     SET DELETED OFF
>     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> will select all records, as expected.  Now, try the following:

>     SELECT Foo
>     GO TOP
>     DELETE    && do not move the record pointer
>     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> Result?  No records, because NOT DELETED('Foo') evaluates to .F. when the
> command executes.  Continue with:

>     SELECT Foo
>     SKIP
>     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> Since NOT DELETED('Foo') currently evaluates to .T., /all/ the records are
> selected - even the deleted ones.  (Setting DELETED to ON will block those).

> No, I can't explain the logic.

>  - Rush



> > What happens if you went:

> > ... !DELETED('table1') AND !DELETED('table2')

> > would that make it more reliable for multiple tables, if generate somewhat
> > longer select stataments?

> > Damien

> > > On Sat, 20 Oct 2001 19:04:06 +0100, John Holubiak wrote
> > > SELECT-SQL does respect the setting of SET DELETED, so if you're using a
> > > default data session, setting DELETED ON at program startup will filter
> out
> > > any deleted records from your result sets. If you are using private data
> > > sessions, you will need to issue this command for each data session.
> Using
> > > the DELETED() function works for single table queries, but is otherwise
> > > unreliable.

> > > Best wishes,

> > > John



> > >> Hi all,
> > >> As we all know, SELECT - SQL statement doesn't regard deletion mark on
> any
> > >> records. Thus, if mytable has 10 deleted records, ones need to specify
> > >> SELECT myfield1, myfield2, ... FROM mytable WHERE !DELETED() in order
> to
> > >> retrieve just the valid (undeleted) record.

> > >> Q: Is there any other way to retrieve the valid record using SELECT -
> SQL
> > >> statement? It's very troublesome to always include the !DELETED() in
> every
> > >> query in the program.

> > >> TIA
> > >> Willianto



Fri, 09 Apr 2004 15:29:22 GMT  
 Exclude deleted record with SELECT - SQL...
In the process of doing an SQL SELECT, FoxPro can open tables additional
times (under different aliases) in other work areas. It doesn't do this with
a 1-table SELECT.

Consequently, AND NOT DELETED("MyTable") can give unexpected results. Best
to do the folowing:

lcOldDeleted = SET("Deleted")
SET DELETED ON
SELECT ......
SET DELETED &lcOldDeleted

--

Cindy Winegarden  MCSD, Microsoft MVP

http://cindywinegarden.adsl.duke.edu


Quote:
> I hadn't seen this behavior using DELETED("tablename"), but testing your
> code confirmed it. However, issuing

> SELECT Foo
> GO TOP
> DELETE    && do not move the record pointer
> SELECT * FROM Foo WHERE NOT DELETED()    && Omit the tablename

> returned the non-deleted records.

> Best wishes,
> John



> > Fox will evaluate the expression for whatever record is current when the
> SQL
> > command is issued.

> > To simplify, assume that we're on the first record in Foo.dbf; no
records
> > are deleted.  Issuing:

> >     SET DELETED OFF
> >     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> > will select all records, as expected.  Now, try the following:

> >     SELECT Foo
> >     GO TOP
> >     DELETE    && do not move the record pointer
> >     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> > Result?  No records, because NOT DELETED('Foo') evaluates to .F. when
the
> > command executes.  Continue with:

> >     SELECT Foo
> >     SKIP
> >     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> > Since NOT DELETED('Foo') currently evaluates to .T., /all/ the records
are
> > selected - even the deleted ones.  (Setting DELETED to ON will block
> those).

> > No, I can't explain the logic.

> >  - Rush



Fri, 09 Apr 2004 20:24:46 GMT  
 Exclude deleted record with SELECT - SQL...
Fox will open the tables in separate work areas for SQL commands.
DELETED('Foo') does not reference the same table that Fox is using for
the query.  Hence, functions which accept a table alias or work area
for a parameter are not gauranteed to produce the correct results.

From VFP7 help file:

"Be careful when using, in join conditions, functions such as DELETED(
), EOF( ), FOUND( ), RECCOUNT( ), and RECNO( ), which support an
optional alias or work area. Including an alias or work area in these
functions might yield unexpected results. SELECT doesn't use your work
areas; it performs the equivalent of USE ... AGAIN. Single-table
queries that use these functions without an optional alias or work
area will return proper results. However, multiple-table queries that
use these functions even without an optional alias or work area
might return unexpected results."

- Keith

Quote:

> Fox will evaluate the expression for whatever record is current when the SQL
> command is issued.

> To simplify, assume that we're on the first record in Foo.dbf; no records
> are deleted.  Issuing:

>     SET DELETED OFF
>     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> will select all records, as expected.  Now, try the following:

>     SELECT Foo
>     GO TOP
>     DELETE    && do not move the record pointer
>     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> Result?  No records, because NOT DELETED('Foo') evaluates to .F. when the
> command executes.  Continue with:

>     SELECT Foo
>     SKIP
>     SELECT * FROM Foo WHERE NOT DELETED('Foo')

> Since NOT DELETED('Foo') currently evaluates to .T., /all/ the records are
> selected - even the deleted ones.  (Setting DELETED to ON will block those).

> No, I can't explain the logic.

>  - Rush



> > What happens if you went:

> > ... !DELETED('table1') AND !DELETED('table2')

> > would that make it more reliable for multiple tables, if generate somewhat
> > longer select stataments?

> > Damien

> > > On Sat, 20 Oct 2001 19:04:06 +0100, John Holubiak wrote
> > > SELECT-SQL does respect the setting of SET DELETED, so if you're using a
> > > default data session, setting DELETED ON at program startup will filter
>  out
> > > any deleted records from your result sets. If you are using private data
> > > sessions, you will need to issue this command for each data session.
>  Using
> > > the DELETED() function works for single table queries, but is otherwise
> > > unreliable.

> > > Best wishes,

> > > John



> > >> Hi all,
> > >> As we all know, SELECT - SQL statement doesn't regard deletion mark on
>  any
> > >> records. Thus, if mytable has 10 deleted records, ones need to specify
> > >> SELECT myfield1, myfield2, ... FROM mytable WHERE !DELETED() in order
>  to
> > >> retrieve just the valid (undeleted) record.

> > >> Q: Is there any other way to retrieve the valid record using SELECT -
>  SQL
> > >> statement? It's very troublesome to always include the !DELETED() in
>  every
> > >> query in the program.

> > >> TIA
> > >> Willianto



Fri, 09 Apr 2004 22:17:10 GMT  
 Exclude deleted record with SELECT - SQL...
In reply to several posters :

I thought the test (deleted) would be applied to the result, not the
source table. That would explain why it works fine with single table
queries, but when querying multiple tables how would VFP select which
deleted marker to put in the result file -> unpredictable results.

Am I completely wrong here ?

This would also explain why SET DELETED can sort the problem.

I had a similar situation recently where I wanted to select by
recno(). I had to do a single table selection by recno() first, then
combine the results. I ran a test, simplified here (ignoring other
fields selected)

SELECT recno('Source') as RecNum from Source into table ResultFil

gave the ResultFil the same number of records as the Source, with all
the values 1.

SELECT recno() as RecNum from Source into table ResultFil

gave the same number of records, with the correct record numbers.

Mike.

On Sat, 20 Oct 2001 21:50:11 +0700, "Willianto"

Quote:

>Hi all,
>As we all know, SELECT - SQL statement doesn't regard deletion mark on any
>records. Thus, if mytable has 10 deleted records, ones need to specify
>SELECT myfield1, myfield2, ... FROM mytable WHERE !DELETED() in order to
>retrieve just the valid (undeleted) record.

>Q: Is there any other way to retrieve the valid record using SELECT - SQL
>statement? It's very troublesome to always include the !DELETED() in every
>query in the program.

>TIA
>Willianto



Fri, 09 Apr 2004 22:25:06 GMT  
 Exclude deleted record with SELECT - SQL...
Mike,

In general, WHERE applies to the source table and HAVING applies to the
result.

--

Cindy Winegarden  MCSD, Microsoft MVP

http://cindywinegarden.adsl.duke.edu


Quote:
> In reply to several posters :

> I thought the test (deleted) would be applied to the result, not the
> source table. That would explain why it works fine with single table
> queries, but when querying multiple tables how would VFP select which
> deleted marker to put in the result file -> unpredictable results.

> Am I completely wrong here ?

> This would also explain why SET DELETED can sort the problem.

> I had a similar situation recently where I wanted to select by
> recno(). I had to do a single table selection by recno() first, then
> combine the results. I ran a test, simplified here (ignoring other
> fields selected)

> SELECT recno('Source') as RecNum from Source into table ResultFil

> gave the ResultFil the same number of records as the Source, with all
> the values 1.

> SELECT recno() as RecNum from Source into table ResultFil

> gave the same number of records, with the correct record numbers.

> Mike.

> On Sat, 20 Oct 2001 21:50:11 +0700, "Willianto"

> >Hi all,
> >As we all know, SELECT - SQL statement doesn't regard deletion mark on
any
> >records. Thus, if mytable has 10 deleted records, ones need to specify
> >SELECT myfield1, myfield2, ... FROM mytable WHERE !DELETED() in order to
> >retrieve just the valid (undeleted) record.

> >Q: Is there any other way to retrieve the valid record using SELECT - SQL
> >statement? It's very troublesome to always include the !DELETED() in
every
> >query in the program.

> >TIA
> >Willianto



Fri, 09 Apr 2004 23:21:56 GMT  
 Exclude deleted record with SELECT - SQL...
It makes sense that one would get unexpected results with a hard-wired table
name that differs from the alias actually being used. As Cindy says, it's
best just to have SET DELETED ON. If one must SET DELETED OFF and use
DELETED() in a WHERE clause, then process individual queries one table at a
time without the hard-coded alias.

Best wishes,
John


Quote:
> Fox will open the tables in separate work areas for SQL commands.
> DELETED('Foo') does not reference the same table that Fox is using for
> the query.  Hence, functions which accept a table alias or work area
> for a parameter are not gauranteed to produce the correct results.

> From VFP7 help file:

> "Be careful when using, in join conditions, functions such as DELETED(
> ), EOF( ), FOUND( ), RECCOUNT( ), and RECNO( ), which support an
> optional alias or work area. Including an alias or work area in these
> functions might yield unexpected results. SELECT doesn't use your work
> areas; it performs the equivalent of USE ... AGAIN. Single-table
> queries that use these functions without an optional alias or work
> area will return proper results. However, multiple-table queries that
> use these functions - even without an optional alias or work area -
> might return unexpected results."

> - Keith



Sat, 10 Apr 2004 08:39:14 GMT  
 Exclude deleted record with SELECT - SQL...
On Mon, 22 Oct 2001 11:21:56 -0400, "Cindy Winegarden"

Quote:

>Mike,

>In general, WHERE applies to the source table and HAVING applies to the
>result.

Thanks Cindy but ...
        Why 'In general' ? or is that just a turn of phrase ?

Mike



Sat, 10 Apr 2004 14:28:49 GMT  
 Exclude deleted record with SELECT - SQL...
Because you can put a HAVING clause without GROUP BY, and it's equal to a WHERE clause.

Grard.


Quote:
> On Mon, 22 Oct 2001 11:21:56 -0400, "Cindy Winegarden"

> >Mike,

> >In general, WHERE applies to the source table and HAVING applies to the
> >result.
> Thanks Cindy but ...
> Why 'In general' ? or is that just a turn of phrase ?

> Mike



Sat, 10 Apr 2004 15:15:15 GMT  
 Exclude deleted record with SELECT - SQL...

Quote:

>Because you can put a HAVING clause without GROUP BY, and it's equal to a WHERE clause.

>Grard.

All clear - merci.

Mike.



Sat, 10 Apr 2004 16:39:07 GMT  
 Exclude deleted record with SELECT - SQL...
Hi Mike
Not clear at all.
Create Cursor yy (id i, amount n(5,2))
Insert into yy values (1,10)
Insert into yy values (1,20)
Insert into yy values (2,100)
Insert into yy values (2,200)
Insert into yy values (3,2000)
Insert into yy values (3,1000)
Select sum(yy.amount) from yy where amount>=100
3330
Select sum(yy.amount) from yy having amount>=100
3300
Select sum(yy.amount) from yy where amount<1000
330
Select sum(yy.amount) from yy having amount<1000
no rows

-Anders


Quote:

> >Because you can put a HAVING clause without GROUP BY, and it's equal to a
WHERE clause.

> >Grard.
> All clear - merci.

> Mike.



Sun, 11 Apr 2004 00:32:24 GMT  
 Exclude deleted record with SELECT - SQL...
It's not equal in the way VFP handles the clause. The WHERE clause excludes
row from processing before any other processing is done on the row.
-Anders

Quote:

> Because you can put a HAVING clause without GROUP BY, and it's equal to a
WHERE clause.

> Grard.




Quote:
> > On Mon, 22 Oct 2001 11:21:56 -0400, "Cindy Winegarden"

> > >Mike,

> > >In general, WHERE applies to the source table and HAVING applies to the
> > >result.
> > Thanks Cindy but ...
> > Why 'In general' ? or is that just a turn of phrase ?

> > Mike



Sun, 11 Apr 2004 01:41:28 GMT  
 
 [ 15 post ] 

 Relevant Pages 

1. No records when excluding deleted records in SQL

2. Exclude deleted records...

3. Q: excluding deleted records from a relation?

4. Exclude Deleted Records In Local View?

5. VFP6 Select-sql and deleted records

6. SQL select without deleted records

7. SQL Select and Deleted Records

8. Excluding Records From SELECT

9. Different results with SELECT SQL and DELETE SQL.

10. How can I limit the number of records selected with SELECT - SQL in FPW

11. Another Problem with Select and Deleted Records

12. SQL-SELECT returns deleted rows?

 

 
Powered by phpBB® Forum Software