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 |
|
divan
Posting Yak Master
153 Posts |
 Posted - 2012-08-21 : 08:31:01
|
Good morning sql world..... I am using the following command to right justify an amount field RIGHT COALESCE(M.EXP_PREM,0)+COALESCE(REG_PROC_PREM,0) AS TRIBUTE_PREMIUM_AMT ,and am getting the following error message...Incorrect syntax near the keyword 'COALESCE'.any idea??? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-08-21 : 08:58:19
|
RIGHT function takes the right-most N characters of a string. It does not right-justify. Also, it can be used only for character data types such as VARCHAR, NCHAR etc.Most experts would advise to do the right-justification and other types of formatting on the client side/front-end/reporting services etc. You can cast the result to DECIMAL - which is not exactly right justification, but may get you what you want:CAST(COALESCE(M.EXP_PREM,0)+COALESCE(REG_PROC_PREM,0) AS DECIMAL(19,2)) AS TRIBUTE_PREMIUM_AMT , |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-08-21 : 09:58:44
|
| If you absolutely can't do this in the report/client application, this will do the trick:STR(COALESCE(M.EXP_PREM,0)+COALESCE(REG_PROC_PREM,0),19,2)That converts the numbers to a string and formats them 19 characters wide with 2 decimal places. They'll have spaces padded on the left. Change the parameters if you need bigger/smaller or more/less decimals. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-21 : 10:00:33
|
| my first question would where are you trying to display the data?if its in reporting tool i would recommend you to use alignment options present in toolIf this has to be done in sql, i would use STRhttp://msdn.microsoft.com/en-us/library/ms189527.aspxSTR(COALESCE(M.EXP_PREM,0)+COALESCE(REG_PROC_PREM,0),20,2)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2012-08-21 : 15:03:58
|
| Thanks Suniabeck... |
|
|
|
|
|
|
|
|
Right Justify an amount field