Solved

nested select statement that pulls data from more than one table

Posted on 2014-09-15
8
214 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 94
Supress rows in SSRS table based on Like or Soundex 2 40
SQL Statement to Update Email Domain 2 23
MS SQL Inner Join - Multiple Join Parameters 2 23
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

867 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