Solved

Conditional column data using nested SQL select statement

Posted on 2014-09-12
14
372 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 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

829 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