?
Solved

Conditional column data using nested SQL select statement

Posted on 2014-09-12
14
Medium Priority
?
403 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 74

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 77

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 74

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 74

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 74

Accepted Solution

by:
sdstuber earned 2000 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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 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 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 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 74

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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 how to recover a database from a user managed backup
Suggested Courses

743 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