SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help in using nvl function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reacha
Starting Member

49 Posts

Posted - 12/28/2010 :  10:38:50  Show Profile  Reply with Quote
In the following query if

BOXI_REPORT_FIELD.BOXI_FIELD_TYPE is null or space then replace that with unknown

how to do this...

i am trying some thing like this


(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE = null then nvl(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE,'Unknown'))or
(if BOXI_REPORT_FIELD.BOXI_FIELD_TYPE = '' then nvl(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE,'Unknown'))

Thanks,
reacha

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 12/28/2010 :  10:41:43  Show Profile  Reply with Quote
[BOXI_FIELD_TYPE ] = ISNULL(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE,'Unknown')

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

singularity
Posting Yak Master

152 Posts

Posted - 12/28/2010 :  10:42:06  Show Profile  Reply with Quote

case when isnull(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE,' ') = ' ' then 'Unknown' else BOXI_REPORT_FIELD.BOXI_FIELD_TYPE end
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/28/2010 :  10:42:59  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
coalesce(nullif(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE.''),'unknown')

Are you using sql server or oracle?


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nigelrivett on 12/28/2010 10:44:30
Go to Top of Page

reacha
Starting Member

49 Posts

Posted - 12/28/2010 :  10:44:47  Show Profile  Reply with Quote
SQL server
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/28/2010 :  10:51:35  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
then equivalent of nvl is coalesce but as you need to cater for a space as well then the nullif will convert the space to a null allowing coalesce to be used as in my previous post.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22777 Posts

Posted - 12/29/2010 :  05:42:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
or

case when BOXI_REPORT_FIELD.BOXI_FIELD_TYPE>'' then BOXI_REPORT_FIELD.BOXI_FIELD_TYPE else 'unknown' end


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000