Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# oracle sql analytics

Posted on 2014-12-04
Medium Priority
75 Views
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

``````

any help is appreciated
0
Question by:tonMachine100

LVL 8

Expert Comment

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
``````
0

LVL 14

Expert Comment

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;
``````
0

LVL 14

Expert Comment

ID: 40480464
0

LVL 78

Assisted Solution

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
``````

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;
``````
0

LVL 14

Expert Comment

ID: 40480598
Yes, slightwv's regex will work better upon the (sample) data provided...
0

LVL 32

Accepted Solution

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 78

Expert Comment

ID: 40480890
Knew there had to be a cleaner way...
0

## Featured Post

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
###### Suggested Courses
Course of the Month11 days, 12 hours left to enroll