Re: SQL question

Hi Ron

>
>Hi,
>
>I'm a beginner at SQL, but thought I had a good handle on things, until
>this report problem came up. It seems like it should be possible, but
>I can't figure out how to do it. Is it possible with one query?
>
>Here is a simplified version of the problem:
>
>I have 2 tables, one called 'customer' and one called 'transactions'.
>The customer table has customer numbers and last names of customers, and
>the transaction table has customer numbers and dates for each time each
>customer has used the "system" (one row for each access).
>
>I would like to create a report to print out the name of each customer
>and the last time they accessed the system. Note that some of the
>customers have never accessed the system, but I still want to print out
>their name (and blank or something else in the last access column).
>
>Seems like it should be easy, but I can't figure it out.
>
>Any help would be appreciated.
>
>Sincerely,
>
>#Ron
>ronnie_at_space.mit.edu

Why don't you try something like this:

SELECT c.customer_number,

               c.customer_name,
               t.access_date
FROM     transactions t,
               customer      c
WHERE  c.customer_number = t.customer_number (+)
AND        NVL(t.access_date,TO_DATE('01-JAN-2199','DD-MON-YYYY')) =
                     (SELECT NVL(MAX(t2.access_date),

TO_DATE('01-JAN-2199','DD-MON-YYYY'))
                      FROM    transactions t2
                      WHERE t2.customer_number = c.customer_number)

I haven't tested it. My only concern would be whether the sub-query would return a NULL value for a customer who had no transaction records. It *should* return NULL, as you are using a group function "MAX". Hope this helps

Mark Griffiths
Freelance Oracle Consultant Received on Wed Mar 18 1998 - 00:00:00 CST