Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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

0
mariita
Asked:
mariita
2 Solutions
 
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
 
Phillip BurtonCommented:
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

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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now