Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
s_anr
Yak Posting Veteran
81 Posts |
 Posted - 2009-07-05 : 04:07:34
|
| I have a table with the column ref_num (Character Type for ref_num : nvarchar(30)). I use a simple query: select ref_num from tickets which works fine. But I'd like to get the ref_num as integer and get NONE for null values.The query blow does not work :isnull(cast(ref_num as INT), 'NONE')Error : Conversion failed when converting the varchar value 'NULL' to data type intAny Suggestions? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-05 : 04:10:30
|
Once again, you can't mix datatypes like this!In your other topic, we discussed this in detail.However, you can do like thisCOALESCE(NULLIF(ref_num, 'NULL'), 'NONE')
Microsoft SQL Server MVPN 56°04'39.26"E 12°55'05.63" |
 |
|
|
s_anr
Yak Posting Veteran
81 Posts |
Posted - 2009-07-05 : 04:54:59
|
| u r right peso but the output when taken in excel is displayd as text and nt no. i hav to manually change it |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-05 : 05:06:14
|
| if you want to show as no then why set default value as none.set it as some no like -1 |
|
|
|
s_anr
Yak Posting Veteran
81 Posts |
Posted - 2009-07-05 : 05:15:47
|
| This report is used further in an xcel pivot report . The datatype is varchar in database. i'd like it to be number in excel output if valu is present and 'NULL' or 'NONE' if value is not present. does this help? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-05 : 05:21:03
|
| then why use coalesce at all? just selecting the field value will bring NULL values also |
|
|
|
s_anr
Yak Posting Veteran
81 Posts |
Posted - 2009-07-05 : 05:35:05
|
| i am using SSRS to schedule this report. I gt this report as an excel attachment in email. in this attachment the ref_num is displayd as txt and not as number. i want it displayd as no. and as null / none if value is nt present.in other words, the report that i get from ssrs dos nnot show NULL values. It shows as blank. If th report shows NULL, i dont have to go thru these conversions |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-05 : 12:54:49
|
| then use NULLIF(yourfield,'') to convert blank values to NULL |
|
|
|
|
|
|
Using ISNULL with CAST