Solved

oracle sql

Posted on 2011-09-21
11
535 Views
Last Modified: 2012-05-12
attached is a data dump file for a schema called test

We need to Generate a list of unique email addresses with the latest name, gender
and age for a user with that email
The selection criteria limit the list to users which never subscribed to
anything; or;
users with inactive subscriptions; or;
users with active subscriptions that renewed between Sep 1st and sep
30th of any year

sql output should be like as far as i'm understanding:


a@a.com m 31 robert

b@b.com f 22 lulu

c@c.com f 08 kim

d@d.com m 22 Jay

e@e.com f 60 Will


 TES.DMP
0
Comment
Question by:it-rex
  • 6
  • 3
  • 2
11 Comments
 
LVL 11

Author Comment

by:it-rex
ID: 36575311
so far
I have this code which is not returning the correct result


SELECT email,
       gender,
       age,
       name
  FROM test.users usr, TEST.SUBSCRIPTIONS sub, TEST.TRANSACTIONS trans
 WHERE usr.user_id = sub.user_id
       AND sub.SUBSCRIPTION_ID = trans.SUBSCRIPTION_ID
       
       
       AND ( (    SUB.ACTIVE_INDICATOR = 'yes'
              AND trans.action = 'renewal'
              AND TO_CHAR (trans.TIMESTAMP, 'mm') = '09')
             
            OR SUB.ACTIVE_INDICATOR = 'no'
           )


as I am still missing the piece of

The selection criteria limit the list to users which never subscribed to
anything
0
 
LVL 7

Accepted Solution

by:
Jacobfw earned 500 total points
ID: 36575350
Try this one:

select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
                    from   users u2
                    where  u2.email = u1.email)
and not exists (select 1
                 from   subscriptions s1
                 where  s1.user_id = u1.user_id)
union
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
                    from   users u2
                    where  u2.email = u1.email)
and exists  (select 1
                 from   subscriptions s1
                 where  s1.user_id = u1.user_id
                 and    active_indicator = 'No')  
union
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
                    from   users u2
                    where  u2.email = u1.email)
and exists  (select 1
                 from   subscriptions s1, transactions t1
                 where  s1.user_id = u1.user_id
                        and    t1.subscription_id = s1.subscription_id
                        and    substr(t1.timestamp,6,3) = 'sep'                                   and    active_indicator = 'yes')


0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36575381
>> between Sep 1st and sep 30th of any year

This requirement almost makes this sound like homework or an exam question.

>>which never subscribed to anything

Look at outer joins.

I cannot import your file due to policy where I work but try this:

...
WHERE usr.user_id = sub.user_id(+)
...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36575393
>>Try this one:

Why hit the table three times?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36575619
>>Try this one:

Why hit the table three times?

Only to keep it simple and demonstrate the three criteria seperately.  In addition, outer joins (+) and combinations of "and/or" / "exists/not exists" can be very tricky.  There is some overhead and always to opportunity to rewrite the code for performance issues later.
0
 
LVL 11

Author Comment

by:it-rex
ID: 36575719
I'm still missing thses entries

a@a.com m 31 robert

d@d.com m 22 Jay

and I think I need them as per the BRS
0
 
LVL 11

Author Comment

by:it-rex
ID: 36575726
@slightwv:
>> between Sep 1st and sep 30th of any year

This requirement almost makes this sound like homework or an exam question.


why?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36575741
My Query returns:
a@a.com      m      31      robert
b@b.com      f      22      lulu
c@c.com      f      08      kim
d@d.com      m      22      jay
e@e.com      f      60      will

those indicated entries are included.
0
 
LVL 11

Author Comment

by:it-rex
ID: 36575746
yours works
and    substr(t1.timestamp,6,3) = 'sep'    
I have changed
to    and    TO_CHAR (t1.TIMESTAMP, 'mm') = '09'

and it works
0
 
LVL 11

Author Comment

by:it-rex
ID: 36575752
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
                    from   users u2
                    where  u2.email = u1.email)
and not exists (select 1
                 from   subscriptions s1
                 where  s1.user_id = u1.user_id)
union
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
                    from   users u2
                    where  u2.email = u1.email)
and exists  (select 1
                 from   subscriptions s1
                 where  s1.user_id = u1.user_id
                 and    active_indicator = 'No')  
union
select email, gender, age, name
from users u1
where u1.User_id = (select max (u2.user_id)
                    from   users u2
                    where  u2.email = u1.email)
and exists  (select 1
                 from   subscriptions s1, transactions t1
                 where  s1.user_id = u1.user_id
                        and    t1.subscription_id = s1.subscription_id
                        and    TO_CHAR (t1.TIMESTAMP, 'mm') = '09'
                                   and    active_indicator = 'yes')
0
 
LVL 11

Author Closing Comment

by:it-rex
ID: 36575754
thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question