Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

oracle sql

Posted on 2011-09-21
11
Medium Priority
?
553 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 2000 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 78

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
Industry Leaders: 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 78

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month21 days, 5 hours left to enroll

810 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