About

This section covers advanced SQL topics.

I do not consider myself an SQL guru, so those who are gurus might not consider some of these topics or the information in this section as being advanced.

Views

Views are a great feature available in most 3rd party RDBMSs. The Omnis data file does not support views.

If you have worked with query classes that join multiple tables, then you already have a sense for what a view is. A view is like a table, but it is based on a SELECT from one or more tables. A view is a pseudo table.

Once you get into using views, you will find multiple uses for them.

1. Joining multiple tables.
2. Multiple joins to the same table.
3. Reduced set of records by filtering them in the view.
4. Eliminate complex SQL in your application by moving it to a view on the RDBMS.

After you create a VIEW, you can map a schema class to the VIEW, treating it the same an a TABLE in the database.

Multiple Joins to the Same Table

Doing multiple joins to the same table is a tricky deal. The Omnis data file does not support multiple joins to the same table. You can only accomplish this with 3rd party RDBMSs.

There are 2 techniques for doing multiple joins:

1. Using table aliases in the SELECT statement.
2. Creating a VIEW of the table, and then using the VIEW in the SELECT

In this tip, we will create and use a VIEW. For this example we'll use the following tables:

1. Contact table with columns for Contact_pkey, EmailAddr, FormalName
2. Email table with columns for Email_pkey, DateSent, Subject, Body, Contact_fkey_To, Contact_fkey_From

In the real world there would be multiple To addresses, but for this example we'll only consider a single To address,

We want to make a list of emails which includes the From and To names from the Contact table.

Trying to do a simple SELECT is not possible because of the double join to the same table. The following SELECT would result in SQL errors.

SELECT Subject, FormalName, FormalName
FROM Email, Contact
WHERE Contact_fkey_From = Contact_pkey
AND Contact_fkey_To = Contact_pkey

By creating at least one VIEW we can solve the problem.

CREATE VIEW Contact_From AS (
SELECT Contact_pkey AS Contact_pkey_From, EmailAddr AS EmailAddr_From, FormalName AS FormalName_From

)

We can now do a simple SELECT using the VIEW

SELECT FormalName_From, FormalName, Subject, DateSent
FROM Email, Contact_From, Contact
WHERE Contact_fkey_From = Contact_pkey_From
AND Contact_fkey_To = Contact_pkey

Select max(Date)

I need to find the latest membership expiry date for each member in the StudioTips members database.

There are two tables involved:

1. Contact - Stores the EmailAddr, FormalName, and CountryName of members and non-members who download files from the studiotips.net website.
2. Payment - Stores the AmountPaid, StartDate, EndDate for each StudioTips membership.

If someone purchases StudioTips, a Contact record is created if one doesn't already exist for the new member, and a Payment record is inserted with the EndDate (Expiry Date) being set to 12 months after the purchase date. Each year that they renew their membership a new Payment record is inserted with an EndDate that is 12 months after the preceeding EndDate.

There will be multiple records in the Payment table for the same member, each with a different EndDate (ExpiryDate).

So here's the problem: I want to send an email to all StudioTips members who's membership has expired on 2006-12-31.

If I did a simple select...

SELECT Payment WHERE EndDate = CAST('2006-12-31' AS DATE)

... I would end up get simply get all Payment records with an EndDate on 2006-12-31, regardless of whether or not they renewed, and there is another payment record for the same member with an EndDate of 2007-12-31.

How can I do a SELECT and just get the last payment expiry date for each contact?

The SELECT MAX() SQL function allows me to select the maximum date from a set of records, but I need to limit the set of Payment records included in the SELECT MAX() to each contact.

The following SELECT does the trick:

SELECT Contact_pkey AS Contact_pkey_expirydate,
(SELECT MAX(ALL,EndDate) FROM Payment WHERE Payment.Contact_fkey = Contact_pkey) AS ExpiryDate
FROM Contact

If I create a VIEW with this SELECT I'll be able to use the view in my select.

To create the view I simply as CREATE VIEW AS in front of the SELECT.

CREATE VIEW Expirydate AS (

SELECT Contact_pkey AS Contact_pkey_expirydate,
(SELECT MAX(ALL,EndDate) FROM Payment WHERE Payment.Contact_fkey = Contact_pkey) AS ExpiryDate
FROM Contact
)

Now I can use the Expirydate VIEW in my SELECT to find all members who's membership expired on 2006-12-31.

SELECT EmailAddr, FormalName, ExpiryDate
FROM Contact, Expirydate
WHERE Contact_pkey_expirydate = Contact_pkey
AND ExpiryDate = CAST('2006-12-31' AS DATE)

StudioTips Documentation - Copyright 2006 Vencor Software