On Fri, 2003-12-12 at 08:09, Yogesh Vachhani wrote:
> For example, if the Phones column is a collection, one can find all
> rows that contain the phone number 206-481-4442 by specifying the
> following SELECT statement:
>
> SELECT LastName
> FROM Contacts
> WHERE Phones(ANY) = "2064814442"
Why not encode Phones as ":phonenumber:"
that way you can already do:
SELECT LastName FROM Contacts WHERE Phones LIKE '%:phonenumber:%'
(btw, your quoting is wrong)
> If one always store fax numbers in the third element of the Phones
> collection, one could find all rows that contain a particular fax
> number
> like this:
>
> SELECT LastName
> FROM Contacts
> WHERE Phones(3) = "2064814442"
SELECT LastName FROM Contacts WHERE Phones LIKE ':%::%::phonenumber:%';
> For example, lets return to the contact database, which contains a
> Phones column whose
> data type is PhoneStructure, so the Phones column contains the
> columns PhoneType and PhoneNum. The following query could returns
> just the phone number 206-402-9400 and not the phone type for that
> number:
>
> SELECT Phones(*)!PhoneNum
> FROM Contacts
> WHERE LastName = "Calder"
This didn't require any special syntax.
> The following query would return the phone numbers for every contact
> who has a fax number:
>
> SELECT Phones(*)!PhoneNum
> FROM Contacts
> WHERE Phones(ANY).PhoneType = "fax"
Instead of encoding phone numbers as :phonenumber:, you could also use
:type=phonenumber:
> Suppose that we put the PhoneType and PhoneNum in a separate table
> instead of in a PhoneStructure. To tie rows in the Phones table to a
> contact in the Contacts table, we then add a RefTo collection column
> called PhoneRef to the Contacts table. This RefTo column contains the
> Row IDs of one or more rows in the Phones table. To access phone
> numbers in the Phones table, one uses an identical query (except for
> minor name changes for clarity):
>
> SELECT PhoneRef(*)!PhoneNum
> FROM Contacts
> WHERE PhoneRef(ANY).PhoneType = "fax"
This is redundant.
> One can query data in a collection or structure-valued column just as
> one can query data in a conventional table. To the query interface, a
> collection of structures should appears as a logical table with its
> parent table name automatically prefixed. So, for instance, a query
> to find all LineItems in the Invoices table would look like this:
>
> SELECT PartNo, QtyOrdered, UnitPrice, QtyOnHand, EditDate
> FROM Invoices.LineItems
Why exactly do you think the JOIN operator is the wrong tool for this?
If you think this is easier to read, then consider creating VIEWs to
store intermediate queries.
> So one can see that one would not lose anything by using SQLite's
> extended storage model (if implemented). Even though the LineItems
> information is embedded into the Invoices table, one can still write
> a simple query to access it as if it were in an independent table.
Backwards compatibility gets lost.
API changes so programs need to be altered.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
