CODEWITHSUNDEEP
sql-server-2008
Ashing
Ashing

Reputation: 79

How can I replace the NULL or 0 value to dash(-)

I have a table with grades from quarter 1 to quarter 4. When the grade of particular subject is equal to 0.00 or NULL, I want to display it in '-' dash.

Here is my subquery

,(CASE WHEN dtl.STUDENT_GRADE < 1 then '-' else
(SELECT dtl.STUDENT_GRADE WHERE (SELECT LAM_TYPE
 FROM PS_CRSE_COMP_LAM WHERE CRSE_ID = (SELECT CRSE_ID FROM PS_CLASS_TBL WHERE STRM = enrl.STRM AND CLASS_NBR = enrl.CLASS_NBR) AND
CRSE_OFFER_NBR = (SELECT CRSE_OFFER_NBR FROM PS_CLASS_TBL WHERE STRM = enrl.STRM AND CLASS_NBR = enrl.CLASS_NBR) AND 
SEQUENCE_NO = dtl.SEQUENCE_NO )= '1ST_PERIOD')end) AS FIRST_PERIOD

It returns me an error of Arithmetic overflow error converting varchar to data type numeric.

I tried the solution of converting using the CAST and replace but it didn't work for me.

Thanks in advance.

I used MSSQL Server 2008

Upvotes: 2

Views: 2509

Answers (2)

John Woo
John Woo

Reputation: 263813

It is because your subquery is returning a number. In this case, you should convert it to string so it will allow - in the list of values:

(CASE WHEN (dtl.STUDENT_GRADE < 1 OR dtl.STUDENT_GRADE IS NULL)
    THEN '-' 
    ELSE
        CAST((SELECT dtl.STUDENT_GRADE WHERE (SELECT LAM_TYPE
        FROM PS_CRSE_COMP_LAM WHERE CRSE_ID = (SELECT CRSE_ID FROM PS_CLASS_TBL WHERE STRM = enrl.STRM AND CLASS_NBR = enrl.CLASS_NBR) AND
        CRSE_OFFER_NBR = (SELECT CRSE_OFFER_NBR FROM PS_CLASS_TBL WHERE STRM = enrl.STRM AND CLASS_NBR = enrl.CLASS_NBR) 
        AND SEQUENCE_NO = dtl.SEQUENCE_NO )= '1ST_PERIOD') AS NVARCHAR(20))
    end 
) AS FIRST_PERIOD

Upvotes: 1

Marijan Milovec
Marijan Milovec

Reputation: 53

I had a similar problem.

select isnull(example, 0) as example

I dont have sql server here now to try it, but try with that it should replace NULL with 0, or in our case try with dash.

select isnull(example, '-') as example

Upvotes: 0

Related Questions