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 |
|
Pinto
Aged Yak Warrior
590 Posts |
 Posted - 2008-07-30 : 05:37:48
|
| I have the following formula in a crystal report using coalesce.However if any of the fields are null then nothingi s returned at all.COALESCE (Reg_HomeAdd1, N'') + COALESCE (N', ' + Reg_HomeAdd2, N'') + COALESCE (N', ' + Reg_HomeAdd3, N'') + COALESCE (N', ' + Reg_HomeAdd4, N'') + N'. ' + COALESCE("tblPHCR_Register"."Reg_HomePcode",N'')Also this formula returns a blank if one of the fields is null"tblPHCR_Register"."Reg_Title" + ' ' +"tblPHCR_Register"."Reg_Firstnames"+' ' +"tblPHCR_Register"."Reg_Surname" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-30 : 05:55:30
|
Impossible. If one of the columns are NULL, that particular column is translated to an empty string.Also do not use double qoutes to denote a column name.COALESCE (Reg_HomeAdd1, N'') + COALESCE (N', ' + Reg_HomeAdd2, N'') + COALESCE (N', ' + Reg_HomeAdd3, N'') + COALESCE (N', ' + Reg_HomeAdd4, N'') + COALESCE(N'. ' + tblPHCR_Register."eg_HomePcode, N'')
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-30 : 07:05:55
|
| <<Also this formula returns a blank if one of the fields is null"tblPHCR_Register"."Reg_Title" + ' ' +"tblPHCR_Register"."Reg_Firstnames"+' ' +"tblPHCR_Register"."Reg_Surname">>Then use IIF function in CR to check for NULLs and then concatenateMadhivananFailing to plan is Planning to fail |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-30 : 09:43:44
|
| Try running your query directly against the database (through Query Analzer or the Query Tool) rather than through Crystal and see if you get the same results. Crystal sets some funky connection options, so don't start debugging your code in Crystal.Oh, and by the way. Crystal Reports sucks. Just thought you should know.e4 d5 xd5 Nf6 |
|
|
|
|
|
|
|
|
Coalesce and Null