CONVERT - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

CONVERT

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-09-25 : 09:14:43

I have the following script lines and I am getting the following error message..

"The round function requires 2 to 3 arguments."

SELECT

REG_PROC_PREM AS REG_PROC_PREM,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+ COALESCE(PHYS_TMB_PREM,0))

*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0)
*(1.0+COALESCE(NEW_PRAC_DISC,0)/100.0)
*(1.0+COALESCE(CLAIMS_MADE_DEBIT,0)/100.0)
*(1.0+COALESCE(TAIL_NOT_BOUGHT,0)/100.0)
*(1.0+COALESCE(GRIEVANCE,0)/100.0)
*(1.0+COALESCE(FELONY_MISDEMEANOR,0)/100.0)
*(1.0+COALESCE(SICK,0)/100.0)
*(1.0+COALESCE(REVIEW,0)/100.0)
*(1.0+COALESCE(NEW_PRACTICE,0)/100.0)
*(1.0+COALESCE(PART_TIME,0)/100.0)
*(1.0+COALESCE(ABUSE,0)/100.0)
*(1.0+COALESCE(LICENSE,0)/100.0)
*(1.0+COALESCE(MISCONDUCT,0)/100.0)
*(1.0+COALESCE(RELAPSE,0)/100.0)
*(1.0+COALESCE(NO_COVERAGE,0)/100.0)
*(1.0+COALESCE(FDA_APPROVED,0)/100.0)
*(1.0+COALESCE(EXPIRING_COVERAGE,0)/100.0)
*(1.0+COALESCE(IMP_PHYS_PROG,0)/100.0)
*(1.0+COALESCE(NOT_RENEWED,0)/100.0)
*(1.0+COALESCE(OTHER_DEBIT,0)/100.0)
*(1.0+COALESCE(OTHER_CREDIT,0)/100.0)
*(1.0+COALESCE(LOSS_FREQUENCY,0)/100.0)
*(1.0+COALESCE(LONGEVITY_CREDIT,0)/100.0)
*(1.0+COALESCE(VIC_LIAB_DEBIT,0)/100.0)
*(1.0+COALESCE(MISC_DEBIT_1,0)/100.0)
*(1.0+COALESCE(MISC_DEBIT_2,0)/100.0)
+ COALESCE (REG_PROC_PREM,0)) AS EXPR_PREM

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-25 : 09:21:03

Hi,


REG_PROC_PREM AS REG_PROC_PREM,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+ COALESCE(PHYS_TMB_PREM,0), 0 )

--
Chandu

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 09:22:42

Since you are converting the result to decimal using CONVERT (which will round the parameter passed to it), you don't really need the ROUND function. So change it to this:

SELECT 

REG_PROC_PREM AS REG_PROC_PREM,
CONVERT (DECIMAL (10,0),(COALESCE(BASE_PREM,0)+ COALESCE(PHYS_TMB_PREM,0))

....

If you do want to use round, add another parameter (towards the end) which tells the round function how many digits to round to.

....
*(1.0+COALESCE(MISC_DEBIT_1,0)/100.0)
*(1.0+COALESCE(MISC_DEBIT_2,0)/100.0)
+ COALESCE (REG_PROC_PREM,0),0)) AS EXPR_PREM

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-25 : 09:24:16

quote:
Originally posted by sunitabeck

Since you are converting the result to decimal using CONVERT (which will round the parameter passed to it), you don't really need the ROUND function. So change it to this:
SELECT

REG_PROC_PREM AS REG_PROC_PREM,
CONVERT (DECIMAL (10,0),(COALESCE(BASE_PREM,0)+ COALESCE(PHYS_TMB_PREM,0))

....
If you do want to use round, add another parameter (towards the end) which tells the round function how many digits to round to.
....
*(1.0+COALESCE(MISC_DEBIT_1,0)/100.0)
*(1.0+COALESCE(MISC_DEBIT_2,0)/100.0)
+ COALESCE (REG_PROC_PREM,0),0)) AS EXPR_PREM

yeah am sry...... yours is correct

--
Chandu

divan
Posting Yak Master

153 Posts

Posted - 2012-09-25 : 09:47:57

Thanks everyone it worked like a charm!!!!! Have a great day where ever you are....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources