Fun with Views

[Ivan Smahin]

Hello Beatrix,

Wednesday, July 11, 2007, 8:55:48 PM, you wrote:

BW> I'm trying to find duplicates within my database and thought I could
BW> try the view feature. But there are some problems:

BW> 1. Using

BW> create view "CheckDuplicates" as (select "CheckSum", count 
BW> ("CheckSum") as CountOfDup from "Message" group by "CheckSum")

BW> in VStudio works, but

BW> create view if not exists "CheckDuplicates" as (select "CheckSum",  
BW> count("CheckSum") as CountOfDup from "Message" group by "CheckSum")

BW> as per wiki gives the errors:

BW> 19:23:54: Error: line 1:13: unexpected token: if
BW> 19:23:54: Error: Kernel error: 0x71000
BW> 19:23:54: Error: line 1:13: unexpected token: if

Sorry we need to fix it in the wiki.
It should be
CREATE OR REPLACE VIEW "CheckDuplicates" as ( ...


BW> 2. How do I show the newly created with in the SQLBuilder in VStudio?
BW> Shouldn't there be a third category Views next to Tables and Links?

Yes it should. But it seems not implemented yet.

BW> 3. Tried to build my SQL by hand:

BW> select * from Message inner join CheckDuplicates on  
BW> CheckDuplicates.CheckSum = Message.CheckSum where  
BW> CheckDuplicates.CountOfDup > 1

BW> But this only give a lovely hard crash within VStudio.

I will check it. Would you like place it to the Mantis?


BW> 4. The new view doesn't show up in auto complete as the other tables  
BW> and fields do.

BW> 5. The SQLBuilder has a "white zone" of about two centimeters on the
BW> right side and field names are cut off in this area.

Also need to be implemented.


BW> Using VStudio 3.1 and normally v4rb 3.1 on Realbasic 2007R3. Is this  
BW> supposed to work? I haven't used views before, but I use a technique  
BW> like this in Access to fish out duplicates with a saved query. The  
BW> database itself is a plain vanilla one with a 1:m relation between  
BW> the tables. The field CheckSum contains (guess what) checksum  
BW> information.

Yes it is nothing special and should be working.




-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com