Re: [sqlite] Something to think about: Saving Select ID Lists

[Igor Tandetnik]

"MoDementia" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> One thing that I really miss form another database is the ability to
> reuse select statements.
>
> Select * FROM Transactions Where DateTime Between x and y Saving
> Unique DataID to 1

How about

create temp view view1 as
Select distinct DataID FROM Transactions Where DateTime Between x and y;

> GetList 1

I'm not sure what that's supposed to do, but perhaps

select * from view1;

> Select * From Data Using 1

It's not clear what that's supposed to do either, but perhaps

select * from Data where ID in (select DataID from view1);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Something to think about: Saving Select ID Lists

[MoDementia]

One thing that I really miss form another database is the ability to reuse
select statements.

I haven't programmed but I believe this is sort of possible using C, this is
a command language version maybe?

 

e.g.

 

 CREATE TABLE IF NOT EXISTS Transactions (ID Integer PRIMARY KEY
AutoIncrement, DataID Integer, DateTime Real, Comment Text COLLATE IUNICODE)

This has lots of entries (INSERTS, DELETES, UPDATES)

 

CREATE TABLE IF NOT EXISTS Data (ID Integer PRIMARY KEY AutoIncrement, Data1
Text, Data2 Integer, LastTransaction Real)

This doesn't have many entries (comparatively)

 

Select * FROM Transactions Where DateTime Between x and y Saving Unique
DataID to 1

 

GetList 1

 

Select * From Data Using 1

 

Select Data2 From Data Where Data2=Z Using 1

 

 

The "Saving Unique DataID to 1" saves a list of ID's that can be used like
part of an IN(1) on another table.

The unique qualifier restricts the list to only 1 unique ID where it is in
multiple rows.

 

If I remember correctly the lists where saved as UsernameList1,
UsernameList2 etc and cleared when disconnected logged out.

 

Not only could you reuse  process intensive ID lists, many complicated joins
etc can be eliminated.

 

Select * FROM Transactions Where DateTime Between x and y Saving Unique
DataID to 1 (500 rows)

 

Select * From Data Where Data2=Z Using 1 (150 rows) Saving ID to 2 (unique
not used as the ID is unique already)

 

Select * From AnotherTable Where Data7<>B Using 2 (25 rows returned)

 

If you need information from the other tables you can do the join statement
here with only 25 IDs.

 

I hope this makes sense

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users