Solved

oracle sql

Posted on 2011-09-21
11
521 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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
dates - loop 12 65
Oracle Next Available Number 2 41
sort a spool into file output in oracle 1 28
Require data to appear on a single line 2 39
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

785 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