Solved

Conditional column data using nested SQL select statement

Posted on 2014-09-12
14
376 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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 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
 
LVL 74

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 74

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…

763 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