[Resolved] Help with CASE statement

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Help with CASE statement

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-05 : 08:29:02

I have following 2 lines where I need to apply CASE


convert(char(2), month(bm.movement_date)),

convert(char(10), replace(convert(decimal(10,2), bp.gross_quantity / 42.0), '.', '')),

what I need is:

Case when field IS Null then ' ' else field end

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-05 : 08:46:25

[code]
isnull(convert(char(2), month(bm.movement_date)),''),

isnull(convert(char(10), replace(convert(decimal(10,2), bp.gross_quantity / 42.0), '.', '')),''),
[/code]

or also:

[code]
coalesce(convert(char(2), month(bm.movement_date)),''),

coalesce(convert(char(10), replace(convert(decimal(10,2), bp.gross_quantity / 42.0), '.', '')),''),
[/code]

Corey

I Has Returned!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-05 : 09:11:27

[code]
CASE
WHEN convert(char(2), month(bm.movement_date)) IS NULL THEN ''
ELSE convert(char(2), month(bm.movement_date))
END,
CASE
WHEN convert(char(10), replace(convert(decimal(10,2), bp.gross_quantity / 42.0), '.', '')) IS NULL THEN ''
ELSE convert(char(10), replace(convert(decimal(10,2), bp.gross_quantity / 42.0), '.', ''))
END,
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-05 : 11:47:08

Great, thank you guys for the options....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources