Defining Query Groups

I'm running a query that is grabbing 2 groups of people that meet one criteria or another, ie:

SELECT a.user_id, n.mailerdate, n.mailername, p.datepurchased
FROM userdb a, purchase p, mailerlists n, (select max(u.dt) as maxdate, u.user_id FROM uservisit u group by u.user_id ) z
WHERE a.user_id = p.user_id(+)
AND a.user_id = z.user_id(+)
AND ((z.maxdate > n.mailerdate) or (p.datepurchased > n.mailerdate))

The 2 criteria are 1: if the last visit (maxdate) is GT the mailerdate OR 2: They have a purchase (datepurchased) GT the mailerdate. The query runs fine, my only issue is being able to differentiate the 2 groups in the output, and defining WHY they are displayed (which criteria they met).

Any help is greatly appreciated.

Try this (without the -'s, of course)

SELECT
---a.user_id,
---n.mailerdate,
---n.mailername,
---p.datepurchased,
---DECODE (
------GREATEST ( z.maxdate, n.mailerdate ),
------z.maxdate,
------DECODE ( z.maxdate, n.mailerdate, 0, 1),
------0 )
------AS BecauseMaxDate---,
---DECODE (
------GREATEST ( p.datepurchased, n.mailerdate ),
------p.datepurchased,
------DECODE ( p.datepurchased, n.mailerdate, 0, 1),
------0 )
------AS BecauseDatePurchased
FROM userdb a, purchase p, mailerlists n, (select max(u.dt) as maxdate, u.user_id FROM uservisit u group by u.user_id ) z
WHERE a.user_id = p.user_id(+)
AND a.user_id = z.user_id(+)
AND ((z.maxdate > n.mailerdate) or (p.datepurchased > n.mailerdate))

So we:
- Check to see if z.maxdate >= n.mailerdate
--- If true, then we need to check if z.maxdate = n.mailerdate

...then do the same thing for the other field.

Let me know if you need it explained further.

Hope this helps,

- Chris

hmmm.. seems doable.. thanks for the help !