Improve company productivity with a Business Account.Sign Up

x
?
Solved

oracle sql analytics

Posted on 2014-12-04
7
Medium Priority
?
77 Views
Last Modified: 2015-02-16
i have a table which records students preferences. i need to flag up the students who have only preferenced 20, 21 or 22.
the table below shows the output from the pref table (2 columns), and the output shows the desired output.

select * from pref			output					
								
student	preference		student	only_20_21_22				
1		1				1		n
1		2				2		n
1		3				3		y
2		1				4		n
2		2				5		y
3		20				6		n
4		21				7		n
4		22				8		y
4		1				9		n
4		2				10		n
5		20						
5		22						
5		21						
6		1						
6		2						
7		1						
7		20						
8		20						
9		20						
9		1						
9		2						
9		22						
10		2						
					
		

Open in new window


any help is appreciated
0
Comment
Question by:tonMachine100
7 Comments
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40480446
Try this:
Select student, CASE 
            WHEN preference = 20 or preference = 21 or preference = 22 
               THEN 'y' 
               ELSE 'n' 
       END as only_20_21_22 from pref

Open in new window

0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40480461
select p.student,
       case regexp_count(listagg(p.preference, ',') within group(order by p.student), '^20|^21|^22')
         when 1 then
          'y'
         else
          'n'
       end only_20_21_22
  from pref p
 group by p.student
 order by p.student;

Open in new window

0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40480464
@Ahmed Merghani: Please see the asker's expected results! Your statement will not work upon what he asked for...
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 79

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40480578
Assuming the sample data provided is not a complete sample and the following data is possible:
student, preference
11,20
11,21
11,22
11,23

Open in new window


The above query will also fail.

Building on it try this:
select p.student,
	case when ltrim(regexp_replace(listagg(p.preference, ',') within group(order by p.student),'2[012]'),',') is null then 'y' else 'n' end 
  from pref p
 group by p.student
 order by p.student;

Open in new window

0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40480598
Yes, slightwv's regex will work better upon the (sample) data provided...
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 40480872
select student,
min(case when not regexp_like(preference,'2[012]') then 'n' else 'y' end) student_only_20_21_22
from pref
group by student
order by student;
0
 
LVL 79

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40480890
Knew there had to be a cleaner way...
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

606 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