nested select statement that pulls data from more than one table

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

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:
u.university_id  is illegal

university only has 2 columns

 UNIVERSITY_CODE
and
UNIVERSITY_EN_NAME
0
sdstuberCommented:
also spectyp is used before it is joined

and uni_institution_type  doesn't have a uni_institution_type_id column
0
mariitaAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
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
mariitaAuthor Commented:
  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
sdstuberCommented:
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

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 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
mariitaAuthor Commented:
Thanks again!
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.