Row Status - for use in SQL commands

Null White
0 White
-1 Finished, grey
1 Exact match, green
2 Fuzzy, magenta
3 Wrong codes, red
4 Locked, yellow
5 Pending, cyan
6 Autopropagated, light green
7 Exact but different codes or numerals, dark magenta
8 Assembled, dark blue
9 Multiexact, dark green

 

Top - Home/Disclaimer

Deleting all entries in a MDB with project ID = "0"

DELETE FROM LanguagePairs WHERE PrjID = 0

Top - Home/Disclaimer

Deleting records whose source is all capitals

Emilio Benito

A DV user asked me how to go about selecting (and possibly deleting) records from an MDB or TDB whose source is all capitals.

SQL Select, MDB:

instr(1,SourceText,Ucase(SourceText),0)>0 and SourceText like '*[a-z]*'

SQL Select, TDB:

instr(1,SourceTerm,Ucase(SourceTerm),0)>0 and SourceTerm like '*[AZ]*'

SQL Execute, MDB:

delete from LanguagePairs where instr(1,SourceText,Ucase(SourceText),0)>0 and SourceText like '*[AZ]*'

SQL Execute, TDB:

delete from TerminologyPairs where instr(1,SourceTerm,Ucase(SourceTerm),0)>0 and SourceTerm like '*[AZ]*'

Note 1. Never run a command (SQL Execute) without selecting the corresponding subset to verify that the records you had in mind are those that will be affected.

Note 2. If you delete records from the database, or otherwise modify records with SQL commands, don't forget to repair the database afterwards. This is to let Déjà Vu reindex the text.

For those proficient with SQL: a simple comparison such as

SourceText = Ucase(SourceText)

is not enough, as this condition will always return TRUE.

The key is using instr with 0 as the last parameter, which forces a binary comparison.

The "and SourceText like '*[AZ]*'" part ensures that rows contain at least one letter.

Top - Home/Disclaimer

Updating Subject and Client in a MDB

UPDATE LanguagePairs SET Client='xxx', Subject='xxx' WHERE PrjID='0000000'

Top - Home/Disclaimer

Painting rows using SQL

E.g.:

UPDATE Pairs SET status_xx = 2 WHERE status_xx = 0

xx = your language

This command will turn fuzzy (2, magenta) into white (0). For color codes, see Row Status.

Top - Home/Disclaimer

Setting a client

Riccardo

UPDATE LanguagePairs SET Client='xxx' WHERE

DATEVALUE(DateStamp)=#mm/DD/yyyy# AND TIMEVALUE (DateStamp)>=#hh:mm:ss# AND

TIMEVALUE(DateStamp)<#hh:mm:Ss#

Use LanguagePairs in the MDB; TerminologyPairs in the TDB; Pairs in your project.

xxx = client number

Use this command as an example for other updates, e.g., subject.

Top - Home/Disclaimer