Conditional column data using nested SQL select statement

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

mariitaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
slightwv (䄆 Netminder) Commented:
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
mariitaAuthor Commented:
I'm getting the following error, which I believe might be due to a missing alias: "%s: invalid identifier"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
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
mariitaAuthor Commented:
I'm getting the error: "column ambiguously defined". Can enrol be used twice as an alias?
0
sdstuberCommented:
>>> 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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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
mariitaAuthor Commented:
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
sdstuberCommented:
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
mariitaAuthor Commented:
The error message says that the error is on line 38, which seems to be the GROUP BY clause
0
mariitaAuthor Commented:
Wow, thanks for your help.
0
sdstuberCommented:
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
mariitaAuthor Commented:
Will do
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.