Type Casting Text to Integer 2

[mbusa]

I have a simple sql statement which i use to populate a selection list in a webpage. The SQL statement returns a NULL too which i dont want to show up in the selection box. So I want that Null value to be replaced by any text string . lets use "blank" for now

case when (vFL_CRPCustomerReference.iStatusSubTypeId) is NULL
then 'Blank'
else (vFL_CRPCustomerReference.iStatusSubTypeId)
end As SubStatusId,


SQL throws back the following error.
Syntax error converting the varchar value 'Blank' to a column of data type int.


How do I typecast it ?

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[gmmastros]

Change it to..

IsNull(vFL_CRPCustomerReference.iStatusSubTypeId, '') as iStatusSubTypeId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JayKusch]

SELECT ISNULL(vFL_CRPCustomerReference.iStatusSubTypeId,'Blank')
From MyTable

Thanks

J. Kusch

 

[gmmastros]

Ooops, my bad.

You have an integer field. If the value is NOT NULL, you want the integer value. If it is null, you want 'blank' (which is a varchar).

You can't have a field that returns 2 different data types.

You could convert the integer to a varchar though.

IsNull(Convert(VarChar(10), vFL_CRPCustomerReference.iStatusSubTypeId), '') as iStatusSubTypeId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mbusa]

Jay .. tried your solution and It still says the same thing


SELECT ISNULL(vFL_CRPCustomerReference.iStatusSubTypeId,'Blank')
from vFL_CRPCustomerReference

Syntax error converting the varchar value 'Blank' to a column of data type int.

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[mbusa]

george. good you caught ur mistake

the 2nd solution works great..thanx..

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]