Defining Query Groups
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Defining Query Groups

  1. #1
    Join Date
    Feb 2000
    Posts
    24

    Question

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  3. #3
    Join Date
    Feb 2000
    Posts
    24
    hmmm.. seems doable.. thanks for the help !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width