Solved

nested select statement that pulls data from more than one table

Posted on 2014-09-15
8
234 Views
Last Modified: 2014-09-15
I'm trying to create a nested select statement that pulls data from more than one table. I'm getting an invalid identifier error, which I'm guessing is because the nested select is not referencing both tables correctly. If I try to include only columns from a single table (by using enrol.special_type_id instead of spectyp.special_type_CODE) then I get a datatype error.

Here is what I have so far:

select    enrol.Rep_Start_Year,  uni.university_code, spectyp.special_type_CODE, count(1) as special_type_Count, spectyp.special_type_EN_DESC
          
    FROM (SELECT rep_start_year, period,
                 CASE
                     WHEN spectyp.special_type_CODE not in ('9.99.98', '9.99.99') THEN 'Other'
                     ELSE spectyp.special_type_CODE
                 END
                     special_type_CODE,
                 fee_cat_type_id,
                 uni_institution_type_id,
                 reg_status_type_id
            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.special_type spectyp
on           enrol.special_type_ID = spectyp.special_type_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

where       enrol.Rep_Start_Year  in ('2012', '2013')
and         enrol.period in ('FAL')
and         regstatus.reg_status_type_code in ('12','13','14','15','16','18')
and         FEETYP.FEE_CAT_TYPE_CODE in ('3', '4')

group by    enrol.Rep_Start_Year, uni.university_code, spectyp.special_type_CODE, spectyp.special_type_EN_DESC
order by    enrol.Rep_Start_Year, uni.university_code, spectyp.special_type_CODE      

Open in new window


Here is an excerpt from the DDL:
  CREATE TABLE "EDCS_UNISTAT"."USTAT_ENROLMENT_DATA" 
   (	 
	"REP_START_YEAR" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"UNI_INSTITUTION_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"PERIOD" VARCHAR2(3 BYTE) NOT NULL ENABLE, 
	"CREDENTIAL_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"COUNTRY_TYPE_ID_CURRENT" NUMBER NOT NULL ENABLE, 
	"COUNTY_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"PROVINCE_STATE_TYPE_ID_PERM" NUMBER NOT NULL ENABLE, 
	"COUNTRY_TYPE_ID_PERM" NUMBER NOT NULL ENABLE, 
	"SENSITIVE_RECORD_FLAG" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"STUDENT_DOB" VARCHAR2(8 BYTE), 
	"LANGUAGE_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"COUNTRY_TYPE_ID_CITZ" NUMBER NOT NULL ENABLE, 
	"STUDENT_OEN" VARCHAR2(9 BYTE) NOT NULL ENABLE, 
	"OUAC_REFNO" VARCHAR2(11 BYTE), 
	"OUAC_APPLNO" VARCHAR2(2 BYTE), 
	"PRIORINSTIT_TYPE_CODE" VARCHAR2(12 BYTE), 
	"REG_STATUS_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"FEE_CAT_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"YEAR_OF_STUDY" NUMBER(2,0) NOT NULL ENABLE, 
	"COLLAB_TYPE_ID" NUMBER NOT NULL ENABLE
)


  CREATE TABLE "EDCS_UNISTAT"."UNI_INSTITUTION_TYPE" 
   (	 
	"UNI_INSTITUTION_TYPE_CODE" VARCHAR2(8 BYTE) NOT NULL ENABLE, 
	"UNI_INSTITUTION_TYPE_EN_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE 
)


  CREATE TABLE "EDCS_CORE"."UNIVERSITY" 
   (	 
	"UNIVERSITY_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	"UNIVERSITY_EN_NAME" VARCHAR2(60 BYTE) NOT NULL ENABLE 
)

  CREATE TABLE "EDCS_UNISTAT"."REG_STATUS_TYPE" 
   (	 
	"REG_STATUS_TYPE_CODE" VARCHAR2(6 BYTE) NOT NULL ENABLE, 
	"REG_STATUS_TYPE_EN_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE 
)

  CREATE TABLE "EDCS_UNISTAT"."FEE_CAT_TYPE" 
   (	 
	"FEE_CAT_TYPE_CODE" VARCHAR2(6 BYTE) NOT NULL ENABLE,
	"FEE_CAT_TYPE_EN_DESC" VARCHAR2(200 BYTE) NOT NULL ENABLE
)

  CREATE TABLE "EDCS_UNISTAT"."SPECIAL_TYPE" 
   (	 
	"SPECIAL_TYPE_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	"SPECIAL_TYPE_EN_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE
)

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
  • 5
  • 3
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40323658
u.university_id  is illegal

university only has 2 columns

 UNIVERSITY_CODE
and
UNIVERSITY_EN_NAME
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40323663
also spectyp is used before it is joined

and uni_institution_type  doesn't have a uni_institution_type_id column
0
 

Author Comment

by:mariita
ID: 40323710
The DDL is a snippet; the real university table has many more columns. The uniinst and uni tables should have had the columns below. How can I join spectyp earlier?

CREATE TABLE "EDCS_UNISTAT"."UNI_INSTITUTION_TYPE" 
   (	"UNI_INSTITUTION_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"UNI_INSTITUTION_TYPE_CODE" VARCHAR2(8 BYTE) NOT NULL ENABLE, 
)

  CREATE TABLE "EDCS_CORE"."UNIVERSITY" 
   (	"UNIVERSITY_ID" NUMBER NOT NULL ENABLE, 
	"UNIVERSITY_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
)

Open in new window

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40323724
looks like there are several other missing columns from other tables.

It's impossible to tell on this end if "invalid identifier" is because a column is really missing or if it's because you left it out

please post complete DDL for the relevant tables
0
 

Author Comment

by:mariita
ID: 40323735
  CREATE TABLE "EDCS_UNISTAT"."USTAT_ENROLMENT_DATA" 
   (	"USTAT_ENROLMENT_DATA_ID" NUMBER NOT NULL ENABLE, 
	"ORGANIZATION_ID" NUMBER NOT NULL ENABLE, 
	"DC_SUBMISSION_PERIOD_ID" NUMBER NOT NULL ENABLE, 
	"REP_START_YEAR" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"UNI_INSTITUTION_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"PERIOD" VARCHAR2(3 BYTE) NOT NULL ENABLE, 
	"PERIOD_START_DATE" VARCHAR2(8 BYTE) NOT NULL ENABLE, 
	"PERIOD_END_DATE" VARCHAR2(8 BYTE) NOT NULL ENABLE, 
	"REPORT_DATE" VARCHAR2(8 BYTE) NOT NULL ENABLE, 
	"PROGRAM_CODE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"CREDENTIAL_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"CREDENTIAL_TYPE_ID_JOINT" NUMBER NOT NULL ENABLE, 
	"PROGRAM_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"PROGRAM_TYPE_ID_JOINT" NUMBER NOT NULL ENABLE, 
	"PROGRAM_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"STUDENT_ID" VARCHAR2(14 BYTE) NOT NULL ENABLE, 
	"TSTUDID_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"PSIS_NSN" VARCHAR2(30 BYTE), 
	"COUNTRY_TYPE_ID_CURRENT" NUMBER NOT NULL ENABLE, 
	"PERMANENT_CITY" VARCHAR2(35 BYTE), 
	"COUNTY_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"PROVINCE_STATE_TYPE_ID_PERM" NUMBER NOT NULL ENABLE, 
	"COUNTRY_TYPE_ID_PERM" NUMBER NOT NULL ENABLE, 
	"SENSITIVE_RECORD_FLAG" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"STUDENT_DOB" VARCHAR2(8 BYTE), 
	"GENDER_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"LANGUAGE_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"COUNTRY_TYPE_ID_CITZ" NUMBER NOT NULL ENABLE, 
	"IMMA_STATUS_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"PROVINCE_STATE_TYPE_ID_1ST" NUMBER NOT NULL ENABLE, 
	"STUD_STATUS_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"EDU_ACT_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"STUDENT_OEN" VARCHAR2(9 BYTE) NOT NULL ENABLE, 
	"INSTITUTION_START_DATE" VARCHAR2(3 BYTE) NOT NULL ENABLE, 
	"OUAC_REFNO" VARCHAR2(11 BYTE), 
	"OUAC_APPLNO" VARCHAR2(2 BYTE), 
	"OUAC_UPREG" VARCHAR2(3 BYTE), 
	"OUAC_TYPE_ID" NUMBER, 
	"OUAC_YEAR_TYPE_ID" NUMBER, 
	"PRIORINSTIT_TYPE_CODE" VARCHAR2(12 BYTE), 
	"CONT_EDU_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"PROGRAM_START_DATE" VARCHAR2(8 BYTE) NOT NULL ENABLE, 
	"MAJOR1" VARCHAR2(10 BYTE), 
	"MAJOR2" VARCHAR2(10 BYTE), 
	"MAJOR3" VARCHAR2(10 BYTE), 
	"PROGRAM_END_DATE" VARCHAR2(8 BYTE), 
	"PGM_STATUS_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"GRADUATION_DATE" VARCHAR2(8 BYTE), 
	"CIP_TYPE_ID_1" NUMBER NOT NULL ENABLE, 
	"CIP_TYPE_ID_2" NUMBER, 
	"CIP_TYPE_ID_3" NUMBER, 
	"REG_PERIOD_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"STATUS_LVL_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"REG_STATUS_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"QUAL_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"QUADCOD_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"QUADCOD_TYPE_ID_2" NUMBER NOT NULL ENABLE, 
	"HONOUR_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"SPECIAL_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"SPECIAL_TYPE_ID_JOINT" NUMBER NOT NULL ENABLE, 
	"SESSION_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"SESSION_COUNT" NUMBER(2,0) NOT NULL ENABLE, 
	"STUDY_STAGE" NUMBER(2,0) NOT NULL ENABLE, 
	"NORMAL_CREDIT_COUNT" NUMBER(4,1) NOT NULL ENABLE, 
	"REGISTERED_CREDIT_COUNT" NUMBER(4,1) NOT NULL ENABLE, 
	"COMPLETED_CREDIT_COUNT" NUMBER(4,1) NOT NULL ENABLE, 
	"DUPLICATE_STUDENT_FLAG" VARCHAR2(1 BYTE), 
	"FEE_CAT_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"FORPOS_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"FTE" NUMBER(4,3) NOT NULL ENABLE, 
	"PSERV_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"POSTGRD_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"EDENTRY_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"YEAR_OF_STUDY" NUMBER(2,0) NOT NULL ENABLE, 
	"COLLAB_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"INELIGIBLE_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"INTEXCH_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"COMMENTS" VARCHAR2(240 BYTE), 
	"START_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
	"END_DATE" DATE, 
	"USER_ID" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"USER_SERIALNO" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"AREA_FLAG" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"FRENCH_PROGRAM_CODE" VARCHAR2(20 BYTE), 
	"STUDENT_LEGAL_GIVEN_NAME" VARCHAR2(50 BYTE), 
	"STUDENT_LEGAL_LAST_NAME" VARCHAR2(100 BYTE), 
	"REPORT_FISCAL_YEAR" NUMBER(4,0) NOT NULL ENABLE, 
	"PERIOD_CODE" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"FIRST_STUDENT_RECORD_FLAG" NUMBER(1,0) NOT NULL ENABLE, 
	"BASIC_INCOME_UNIT_AMOUNT" NUMBER(8,3) NOT NULL ENABLE, 
	"REGISTRATION_FEE_AMOUNT" NUMBER(8,2) NOT NULL ENABLE, 
	"CALC_STUDENT_STUDY_YEAR" NUMBER(4,0) NOT NULL ENABLE, 
	"STUDENT_AGE" NUMBER(3,0) NOT NULL ENABLE, 
	"REG_STUDY_LEVEL_CODE" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"STUDENT_ENROLMENT_CODE" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"DERIVE_STUDENT_STUDY_YEAR" NUMBER(4,0) NOT NULL ENABLE, 
	"OUAC_EVADE_CODE" VARCHAR2(2 BYTE), 
	"DERIVE_STUDY_LEVEL_CODE" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"STUDY_LVL_CAT_CODE" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"STUDY_LVL_SUBCAT_CODE" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
	"PERIOD_SEQUENCE_NUMBER" NUMBER(1,0) NOT NULL ENABLE
)



  CREATE TABLE "EDCS_UNISTAT"."UNI_INSTITUTION_TYPE" 
   (	"UNI_INSTITUTION_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"UNI_INSTITUTION_TYPE_CODE" VARCHAR2(8 BYTE) NOT NULL ENABLE, 
	"UNI_INSTITUTION_TYPE_EN_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"UNI_INSTITUTION_TYPE_FR_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"UNIVERSITY_ID" NUMBER NOT NULL ENABLE, 
	"COMMENTS" VARCHAR2(240 BYTE), 
	"OPEN_DATE" DATE NOT NULL ENABLE, 
	"CLOSE_DATE" DATE, 
	"START_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
	"END_DATE" DATE, 
	"USER_ID" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"USER_SERIALNO" VARCHAR2(30 BYTE) NOT NULL ENABLE
)



  CREATE TABLE "EDCS_CORE"."UNIVERSITY" 
   (	"UNIVERSITY_ID" NUMBER NOT NULL ENABLE, 
	"UNIVERSITY_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
	"UNIVERSITY_EN_NAME" VARCHAR2(60 BYTE) NOT NULL ENABLE, 
	"UNIVERSITY_FR_NAME" VARCHAR2(60 BYTE) NOT NULL ENABLE, 
	"ORGANIZATION_ID" NUMBER NOT NULL ENABLE, 
	"COMMENTS" VARCHAR2(240 BYTE), 
	"OPEN_DATE" DATE NOT NULL ENABLE, 
	"CLOSE_DATE" DATE, 
	"START_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
	"END_DATE" DATE, 
	"USER_ID" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"USER_SERIALNO" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"SEQUENCE_NUMBER" NUMBER(5,0) NOT NULL ENABLE, 
	"OUAC_CODE" VARCHAR2(3 BYTE) NOT NULL ENABLE
)


  CREATE TABLE "EDCS_UNISTAT"."REG_STATUS_TYPE" 
   (	"REG_STATUS_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"REG_STATUS_TYPE_CODE" VARCHAR2(6 BYTE) NOT NULL ENABLE, 
	"REG_STATUS_TYPE_EN_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"REG_STATUS_TYPE_FR_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"COMMENTS" VARCHAR2(240 BYTE), 
	"OPEN_DATE" DATE NOT NULL ENABLE, 
	"CLOSE_DATE" DATE, 
	"START_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
	"END_DATE" DATE, 
	"USER_ID" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"USER_SERIALNO" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"REGISTRATION_STATUS_FLAG" VARCHAR2(1 BYTE) NOT NULL ENABLE
)

  CREATE TABLE "EDCS_UNISTAT"."FEE_CAT_TYPE" 
   (	"FEE_CAT_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"FEE_CAT_TYPE_CODE" VARCHAR2(6 BYTE) NOT NULL ENABLE, 
	"FEE_CAT_TYPE_EN_DESC" VARCHAR2(200 BYTE) NOT NULL ENABLE, 
	"FEE_CAT_TYPE_FR_DESC" VARCHAR2(200 BYTE) NOT NULL ENABLE, 
	"COMMENTS" VARCHAR2(240 BYTE), 
	"OPEN_DATE" DATE NOT NULL ENABLE, 
	"CLOSE_DATE" DATE, 
	"START_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
	"END_DATE" DATE, 
	"USER_ID" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"USER_SERIALNO" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"FUNDING_ELIGIBILITY_FLAG" VARCHAR2(1 BYTE) NOT NULL ENABLE
)

  CREATE TABLE "EDCS_UNISTAT"."SPECIAL_TYPE" 
   (	"SPECIAL_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"SPECIAL_TYPE_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	"SPECIAL_TYPE_EN_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"SPECIAL_TYPE_FR_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"SPEC_GRP_TYPE_ID" NUMBER NOT NULL ENABLE, 
	"COMMENTS" VARCHAR2(240 BYTE), 
	"OPEN_DATE" DATE NOT NULL ENABLE, 
	"CLOSE_DATE" DATE, 
	"START_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
	"END_DATE" DATE, 
	"USER_ID" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"USER_SERIALNO" VARCHAR2(30 BYTE) NOT NULL ENABLE
)

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40323759
as in the previous question I've removed the schema names.

I also restructured the query to remove the extraneous subqueries.
They weren't necessary, nor did they help performance but they did create the join-order problem mentioned previously.

  SELECT rep_start_year,
         university_code,
         special_type_code,
         COUNT(*) AS special_type_count,
         special_type_en_desc
    FROM (SELECT enrol.rep_start_year,
                 spectyp.special_type_en_desc,
                 CASE
                     WHEN special_type_code NOT IN ('9.99.98', '9.99.99') THEN 'Other'
                     ELSE special_type_code
                 END
                     special_type_code,
                 (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)
                     AS university_code
            FROM special_type spectyp
                 INNER JOIN ustat_enrolment_data enrol
                     ON enrol.special_type_id = spectyp.special_type_id
                 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
           WHERE enrol.rep_start_year IN ('2012', '2013')
             AND enrol.period IN ('FAL')
             AND regstatus.reg_status_type_code IN ('12',
                                                    '13',
                                                    '14',
                                                    '15',
                                                    '16',
                                                    '18')
             AND feetyp.fee_cat_type_code IN ('3', '4'))
GROUP BY rep_start_year,
         university_code,
         special_type_code,
         special_type_en_desc
ORDER BY rep_start_year, university_code, special_type_code

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40323765
This is the same as previous post but I've put the schemas back in


  SELECT rep_start_year,
         university_code,
         special_type_code,
         COUNT(*) AS special_type_count,
         special_type_en_desc
    FROM (SELECT enrol.rep_start_year,
                 spectyp.special_type_en_desc,
                 CASE
                     WHEN special_type_code NOT IN ('9.99.98', '9.99.99') THEN 'Other'
                     ELSE special_type_code
                 END
                     special_type_code,
                 (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)
                     AS university_code
            FROM edcs_unistat.special_type spectyp
                 INNER JOIN edcs_unistat.ustat_enrolment_data enrol
                     ON enrol.special_type_id = spectyp.special_type_id
                 INNER JOIN edcs_unistat.uni_institution_type uniinst
                     ON enrol.uni_institution_type_id = uniinst.uni_institution_type_id
                 INNER JOIN edcs_core.university 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
           WHERE enrol.rep_start_year IN ('2012', '2013')
             AND enrol.period IN ('FAL')
             AND regstatus.reg_status_type_code IN ('12',
                                                    '13',
                                                    '14',
                                                    '15',
                                                    '16',
                                                    '18')
             AND feetyp.fee_cat_type_code IN ('3', '4'))
GROUP BY rep_start_year,
         university_code,
         special_type_code,
         special_type_en_desc
ORDER BY rep_start_year, university_code, special_type_code

Open in new window

0
 

Author Closing Comment

by:mariita
ID: 40323775
Thanks again!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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…

688 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