Inconsistent datatypes: expected CHAR got NUMBER

Why am I getting the following error when I run this query? "inconsistent datatypes: expected CHAR got NUMBER"
It seems to be triggered by the YEAR_OF_STUDY case statement.

SELECT rep_start_year,
         university_code,
         YEAR_OF_STUDY,
         COUNT(*) AS YEAR_OF_STUDY_count

    FROM (SELECT rep_start_year,
                 CASE
                     WHEN YEAR_OF_STUDY in (5,6,7,8,9)  THEN 'Other'
                     ELSE
                         YEAR_OF_STUDY
                 END
                     YEAR_OF_STUDY,

                 (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.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 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, YEAR_OF_STUDY
ORDER BY rep_start_year, university_code, YEAR_OF_STUDY

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.

slightwv (䄆 Netminder) Commented:
The return values for a CASE statement have to be of the same data type.

If year_of_study is a number then:
...
CASE
                     WHEN YEAR_OF_STUDY in (5,6,7,8,9)  THEN 'Other'
                     ELSE
                         to_char(YEAR_OF_STUDY)
                 END
                     YEAR_OF_STUDY,
...

Same for the other CASE statement.
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It's lines 8 and 10.

If the YEAR_OF_STUDY, which appears to be a number, is 5, 6, 7, 8 or 9, you return a string. If it isn't, you return a number. That's the mismatch.

Maybe line 10 should be

CAST(YEAR_OF_STUDY as varchar(4))

Open in new window

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.