Solved

Conditional column data using nested SQL select statement

Posted on 2014-09-12
14
339 Views
Last Modified: 2014-09-12
I want to conditionally return column data using a SQL select statement. I'm using Oracle SQL Developer.

The output should look like this:

REP_START_YEAR  UNIVERSITY_CODE     STUDENT_OEN     OEN_COUNT
2012                      UNIVERSITYA              999999999           3
2012                      UNIVERSITYA              000000000           0
2012                      UNIVERSITYA              Other                   2105

This is what I've come up with so far:

select enrol.Rep_Start_Year, uni.university_code,  enrol.student_OEN, count(*) as Invalid_OEN_Count

from (select enrol.Rep_Start_Year, uni.university_code, enrol.student_OEN, CASE WHEN enrol.student_OEN  not in ('999999999','000000000')
                            THEN 'Other'
                          ELSE enrol.student_OEN
                          END as student_OEN
from edcs_unistat.ustat_enrolment_data enrol) 

inner join    edcs_unistat.uni_institution_type uniinst
on        enrol.uni_institution_type_ID = uniinst.uni_institution_type_id

inner join    
(select u.UNIVERSITY_ID,
    (case 
        when U.UNIVERSITY_CODE = 'DOMC' then 'CARL'
        when U.UNIVERSITY_CODE = 'NOSM-LAKE' then 'NOSM'
        when U.UNIVERSITY_CODE = 'NOSM-LAUR' then 'NOSM'
        when U.UNIVERSITY_CODE = 'LAUR-ALGM' then 'ALGM'
        when U.UNIVERSITY_CODE = 'LAUR-HRST' then 'HRST'
        when U.UNIVERSITY_CODE = 'DOMC' then 'CARL'
        else U.UNIVERSITY_CODE 
     end) as university_code
from edcs_core.university u) uni
on          uniinst.university_id = uni.university_id

inner join  edcs_unistat.reg_status_type regstatus
on          enrol.reg_status_type_id = regstatus.reg_status_type_id

inner join  edcs_unistat.fee_cat_type feetyp
on          enrol.fee_cat_type_id = feetyp.fee_cat_type_id

group by    enrol.Rep_Start_Year, uni.university_code,  enrol.student_OEN

order by    enrol.Rep_Start_Year, uni.university_code, enrol.student_OEN

Open in new window

0
Comment
Question by:mariita
  • 7
  • 6
14 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 40319436
I don't have your tables or data to conduct a test, so I'll just have to assume your query works as is.  

If so, then what is the question?  If the query does not work, then what is the failing?


You do have two identical case conditions

 WHEN u.university_code = 'DOMC' THEN 'CARL'

that doesn't cause an error, but it's not helpful either
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40319442
What are the conditions you want on the select?

It should either be a where clause on an 'and <something>' on one or more of the joins.
0
 

Author Comment

by:mariita
ID: 40319478
I'm getting the following error, which I believe might be due to a missing alias: "%s: invalid identifier"
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40319501
I found a couple problems - it would be a LOT easier if you provided ddl for your tables


try this...

  SELECT enrol.rep_start_year,
         uni.university_code,
         enrol.student_oen,
         COUNT(*) AS invalid_oen_count
    FROM (SELECT enrol.rep_start_year,
                 enrol.student_oen,
                 CASE
                     WHEN enrol.student_oen NOT IN ('999999999', '000000000') THEN 'Other'
                     ELSE enrol.student_oen
                 END
                     AS student_oen
            FROM edcs_unistat.ustat_enrolment_data enrol) enrol
         INNER JOIN edcs_unistat.uni_institution_type uniinst
             ON enrol.uni_institution_type_id = uniinst.uni_institution_type_id
         INNER JOIN
         (SELECT u.university_id,
                 (CASE
                      WHEN u.university_code = 'DOMC' THEN 'CARL'
                      WHEN u.university_code = 'NOSM-LAKE' THEN 'NOSM'
                      WHEN u.university_code = 'NOSM-LAUR' THEN 'NOSM'
                      WHEN u.university_code = 'LAUR-ALGM' THEN 'ALGM'
                      WHEN u.university_code = 'LAUR-HRST' THEN 'HRST'
                      WHEN u.university_code = 'DOMC' THEN 'CARL'
                      ELSE u.university_code
                  END)
                     AS university_code
            FROM edcs_core.university u) uni
             ON uniinst.university_id = uni.university_id
         INNER JOIN edcs_unistat.reg_status_type regstatus
             ON enrol.reg_status_type_id = regstatus.reg_status_type_id
         INNER JOIN edcs_unistat.fee_cat_type feetyp
             ON enrol.fee_cat_type_id = feetyp.fee_cat_type_id
GROUP BY enrol.rep_start_year, uni.university_code, enrol.student_oen
ORDER BY enrol.rep_start_year, uni.university_code, enrol.student_oen
0
 

Author Comment

by:mariita
ID: 40319522
I'm getting the error: "column ambiguously defined". Can enrol be used twice as an alias?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40319535
>>> I'm getting the error: "column ambiguously defined".

I can't test anything without the tables, please provide ddl for your tables, or at least a structural description  With tables this probably a 30 second task to figure out. Without, it's likely to take a long time with lots of guessing

>>> Can enrol be used twice as an alias?

depends on the scope in which it's used
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40319581
Here's another guess
again, I can't really test this

  SELECT enrol.rep_start_year,
         uni.university_code,
         enrol.student_oen,
         COUNT(*) AS invalid_oen_count
    FROM (SELECT rep_start_year,
                 CASE
                     WHEN student_oen NOT IN ('999999999', '000000000') THEN 'Other'
                     ELSE student_oen
                 END
                     student_oen,
                 fee_cat_type_id,
                 uni_institution_type_id,
                 reg_status_type_id
            FROM ustat_enrolment_data) enrol
         INNER JOIN uni_institution_type uniinst
             ON enrol.uni_institution_type_id = uniinst.uni_institution_type_id
         INNER JOIN
         (SELECT u.university_id,
                 (CASE
                      WHEN u.university_code = 'DOMC' THEN 'CARL'
                      WHEN u.university_code = 'NOSM-LAKE' THEN 'NOSM'
                      WHEN u.university_code = 'NOSM-LAUR' THEN 'NOSM'
                      WHEN u.university_code = 'LAUR-ALGM' THEN 'ALGM'
                      WHEN u.university_code = 'LAUR-HRST' THEN 'HRST'
                      WHEN u.university_code = 'DOMC' THEN 'CARL'
                      ELSE u.university_code
                  END)
                     AS university_code
            FROM university u) uni
             ON uniinst.university_id = uni.university_id
         INNER JOIN reg_status_type regstatus
             ON enrol.reg_status_type_id = regstatus.reg_status_type_id
         INNER JOIN fee_cat_type feetyp ON enrol.fee_cat_type_id = feetyp.fee_cat_type_id
GROUP BY enrol.rep_start_year, uni.university_code, enrol.student_oen
ORDER BY enrol.rep_start_year, uni.university_code, enrol.student_oen
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 40319667
this should be functionally the same thing as the previous post but a little more compact, and (hopefully) easier to read version


  SELECT rep_start_year,
         university_code,
         student_oen,
         COUNT(*) invalid_oen_count
    FROM (SELECT enrol.rep_start_year,
                 CASE
                     WHEN uni.university_code = 'DOMC' THEN 'CARL'
                     WHEN uni.university_code = 'NOSM-LAKE' THEN 'NOSM'
                     WHEN uni.university_code = 'NOSM-LAUR' THEN 'NOSM'
                     WHEN uni.university_code = 'LAUR-ALGM' THEN 'ALGM'
                     WHEN uni.university_code = 'LAUR-HRST' THEN 'HRST'
                     WHEN uni.university_code = 'DOMC' THEN 'CARL'
                     ELSE uni.university_code
                 END
                     university_code,
                 CASE
                     WHEN enrol.student_oen NOT IN ('999999999', '000000000') THEN 'Other'
                     ELSE enrol.student_oen
                 END
                     student_oen
            FROM ustat_enrolment_data enrol
                 INNER JOIN uni_institution_type uniinst
                     ON enrol.uni_institution_type_id = uniinst.uni_institution_type_id
                 INNER JOIN university uni ON uniinst.university_id = uni.university_id
                 INNER JOIN reg_status_type regstatus
                     ON enrol.reg_status_type_id = regstatus.reg_status_type_id
                 INNER JOIN fee_cat_type feetyp ON enrol.fee_cat_type_id = feetyp.fee_cat_type_id)
GROUP BY rep_start_year, university_code, student_oen
ORDER BY rep_start_year, university_code, student_oen
0
 

Author Comment

by:mariita
ID: 40319759
Both versions give the error message "table or view does not exist"
I tried exporting the DDL, but it was huge. I was only able to export the DDL for one of the two schemas, EDCS_CORE, but not EDCS_UNISTAT
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 40319763
I removed the schema names from the tables in my versions in order to try to simulate what I thought the table structures might be.


Simply put them back in where needed
0
 

Author Comment

by:mariita
ID: 40319772
The error message says that the error is on line 38, which seems to be the GROUP BY clause
0
 

Author Closing Comment

by:mariita
ID: 40319801
Wow, thanks for your help.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40319804
glad I could help.

For future questions please include the ddl, you don't need the whole schema. Only the tables in the query.
0
 

Author Comment

by:mariita
ID: 40320033
Will do
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now