Solved

nested select statement that pulls data from more than one table

Posted on 2014-09-15
8
212 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
  • 5
  • 3
8 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 40323658
u.university_id  is illegal

university only has 2 columns

 UNIVERSITY_CODE
and
UNIVERSITY_EN_NAME
0
 
LVL 73

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

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 73

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 73

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now