Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Arithmetic overflow error converting varchar to data type numeric

Status
Not open for further replies.

JanetStackpole

Programmer
Sep 30, 2005
24
US
Hello,

I am getting the following error when executing a stored procedure in MS SQL 2005. This procedure executes correctly in 2000. Any assistance would be greatly appreciated. I have included the SP

error message
Msg 8115, Level 16, State 6, Procedure CMP_11_ADD_BH_ADMIT, Line 35
Arithmetic overflow error converting varchar to data type numeric.
Code:
CREATE PROCEDURE [dbo].[CMP_11_ADD_BH_ADMIT]

AS

/* DECLARE AND SET DATA SYSTEM AND EXTRACT FILE NAME VARIABLES */
DECLARE
@DataSystemName AS VARCHAR(30),
@ExtractFileName AS VARCHAR (30)
SET @DataSystemName = 'BH'
SET @ExtractFileName = 'BH'


TRUNCATE TABLE ABD_MI50_User.dbo.ABD_BH_ADMIT

INSERT INTO ABD_MI50_User.dbo.ABD_BH_ADMIT
(
PAT_ID, ATT_PROV, ADM_DATE, END_DATE, DIS_STAT, ICD9_DIAG_01, ICD9_DIAG_02, ABD_RECTYPE, ABD_LEVEL_OF_CARE,
.......	)
SELECT
/*R*/	--PAT_ID,
	UCI,
/*R*/	--ATT_PROV,
	RIGHT('000000000000' + LTRIM(RTRIM(UPID)),12),
	--ADM_DATE,
	CASE
		WHEN LEN(ADMDATE) = 0 THEN NULL
		WHEN ISDATE(ADMDATE) = 1 THEN CAST(ADMDATE AS DATETIME)
		ELSE NULL
		END,
	--END_DATE,
	CASE
		WHEN LEN(LASTDTSVC) = 0 THEN NULL
		WHEN ISDATE(LASTDTSVC) = 1 THEN CAST(LASTDTSVC AS DATETIME)
		ELSE NULL
		END,
	--DIS_STAT,
	CASE
		WHEN LEN(LTRIM(DISDSCHG)) = 0 THEN NULL
		WHEN ISDATE(DISDSCHG) = 1 THEN CAST(DISDSCHG AS DATETIME)
		ELSE NULL
		END,
	--ICD9_DIAG_01,
	CASE
		WHEN LEN(LTRIM(PRIMDX)) = 0 THEN NULL
		ELSE PRIMDX
		END,
	--ICD9_DIAG_02,
	CASE
		WHEN LEN(LTRIM(SECONDX)) = 0 THEN NULL
		ELSE SECONDX
		END,
-- CLIENT SPECIFIC FIELDS:
	--ABD_RECTYPE,
	CASE
		WHEN LEN(LTRIM(RECTYPE)) = 0 THEN NULL
		ELSE RECTYPE
		END,
	--ABD_LEVEL_OF_CARE,
	CASE
		WHEN LEN(LTRIM(LOCAOD)) = 0 THEN NULL
		ELSE LOCAOD
		END,
	--ABD_CLOSURE_DT,
	CASE
		WHEN LEN(CLOSDATE) = 0 THEN NULL
		WHEN ISDATE(CLOSDATE) = 1 THEN CAST(CLOSDATE AS DATETIME)
		ELSE NULL
		END,
	--ABD_CODEPEND,
	CASE
		WHEN LEN(LTRIM(CODEPEND)) = 0 THEN NULL
		ELSE CODEPEND
		END,
	--ABD_PATCTRLNO,
	CASE
		WHEN LEN(LTRIM(PATCTRLNO)) = 0 THEN NULL
		ELSE PATCTRLNO
		END,
	--ABD_REFBYCODE,
	CASE
		WHEN LEN(LTRIM(REFBYCODE)) = 0 THEN NULL
		ELSE REFBYCODE
		END,
	--ABD_REFBY_PROV,
	CASE
		WHEN LEN(LTRIM(RTRIM(REFBYUPID))) = 0 THEN NULL
		ELSE RIGHT('000000000000' + LTRIM(RTRIM(REFBYUPID)),12)
		END,
	--ABD_EDU_LEVEL,
	CASE
		WHEN LEN(LTRIM(EDLEVEL)) = 0 THEN NULL
		ELSE EDLEVEL
		END,
	--ABD_EDU_TYPE,
	CASE
		WHEN LEN(LTRIM(EDTYPE)) = 0 THEN NULL
		ELSE EDTYPE
		END,
	--ABD_EMP_STATUS,
	CASE
		WHEN LEN(LTRIM(EMPSTATUS)) = 0 THEN NULL
		ELSE EMPSTATUS
		END,
	--ABD_INCSOURCE,
	CASE
		WHEN LEN(LTRIM(INCSOURCE)) = 0 THEN NULL
		ELSE INCSOURCE
		END,
	--ABD_LIVING_ARR,
	CASE
		WHEN LEN(LTRIM(LIIVINGARR)) = 0 THEN NULL
		ELSE LIIVINGARR
		END,
	--ABD_IPH_EPI,
	CASE
		WHEN LEN(LTRIM(RTRIM(INPATEPI))) = 0 THEN NULL
		WHEN ISNUMERIC(INPATEPI) = 1 THEN CAST(INPATEPI AS NUMERIC)
		ELSE NULL
		END,
	--ABD_INT_OPH_EPI,
	CASE
		WHEN LEN(LTRIM(RTRIM(IOPEPI))) = 0 THEN NULL
		WHEN ISNUMERIC(IOPEPI) = 1 THEN CAST(IOPEPI AS NUMERIC)
		ELSE NULL
		END,
	--ABD_GEN_OPH_EPI,
	CASE
		WHEN LEN(LTRIM(RTRIM(OPEPI))) = 0 THEN NULL
		WHEN ISNUMERIC(OPEPI) = 1 THEN CAST(OPEPI AS NUMERIC)
		ELSE NULL
		END,
	--ABD_REHAB_EPI,
	CASE
		WHEN LEN(LTRIM(RTRIM(REHABEPI))) = 0 THEN NULL
		WHEN ISNUMERIC(REHABEPI) = 1 THEN CAST(REHABEPI AS NUMERIC)
		ELSE NULL
		END,
	--ABD_DX_TYPE,
	CASE
		WHEN LEN(LTRIM(RTRIM(DXTYPE))) = 0 THEN NULL
		ELSE DXTYPE
		END,
	--ABD_METHADONE,
	CASE
		WHEN LEN(LTRIM(RTRIM(METHADONE))) = 0 THEN NULL
		ELSE METHADONE
		END,
	--ABD_NUM_CHILD,
	CASE
		WHEN LEN(LTRIM(RTRIM(NOCHILD))) = 0 THEN NULL
		WHEN ISNUMERIC(NOCHILD) = 1 THEN CAST(NOCHILD AS NUMERIC)
		ELSE NULL
		END,
	--ABD_SMDSE,
	CASE
		WHEN LEN(LTRIM(RTRIM(SMDSED))) = 0 THEN NULL
		ELSE SMDSED
		END,
	--ABD_AODABUSE,
	CASE
		WHEN LEN(LTRIM(RTRIM(AODABUSE))) = 0 THEN NULL
		ELSE AODABUSE
		END,
	--ABD_FORENSIC,
	CASE
		WHEN LEN(LTRIM(RTRIM(FORENSIC))) = 0 THEN NULL
		ELSE FORENSIC
		END,
	--ABD_DD,
	CASE
		WHEN LEN(LTRIM(RTRIM(DD))) = 0 THEN NULL
		ELSE DD
		END,
	--ABD_MIMR_DUAL,
	CASE
		WHEN LEN(LTRIM(RTRIM(MIMRDUAL))) = 0 THEN NULL
		ELSE MIMRDUAL
		END,
	--ABD_DUI,
	CASE
		WHEN LEN(LTRIM(RTRIM(DUIDWI))) = 0 THEN NULL
		ELSE DUIDWI
		END,
	--ABD_DEAF,
	CASE
		WHEN LEN(LTRIM(RTRIM(DEAF))) = 0 THEN NULL
		ELSE DEAF
		END,
	--ABD_HEAR_IMP,
	CASE
		WHEN LEN(LTRIM(RTRIM(HEARIMP))) = 0 THEN NULL
		ELSE HEARIMP
		END,
	--ABD_BLIND,
	CASE
		WHEN LEN(LTRIM(RTRIM(BLIND))) = 0 THEN NULL
		ELSE BLIND
		END,
	--ABD_VIS_IMP,
	CASE
		WHEN LEN(LTRIM(RTRIM(VISIMP))) = 0 THEN NULL
		ELSE VISIMP
		END,
	--ABD_PHYS_DISAB,
	CASE
		WHEN LEN(LTRIM(RTRIM(PHYSDISAB))) = 0 THEN NULL
		ELSE PHYSDISAB
		END,
	--ABD_SPEECH_IMP,
	CASE
		WHEN LEN(LTRIM(RTRIM(SPEECHIMP))) = 0 THEN NULL
		ELSE SPEECHIMP
		END,
	--ABD_PHYSABU_VIC,
	CASE
		WHEN LEN(LTRIM(RTRIM(PHYSABUVIC))) = 0 THEN NULL
		ELSE PHYSABUVIC
		END,
	--ABD_SEXABU_VIC,
	CASE
		WHEN LEN(LTRIM(RTRIM(SEXABUVIC))) = 0 THEN NULL
		ELSE SEXABUVIC
		END,
	--ABD_DOMVIOL,
	CASE
		WHEN LEN(LTRIM(RTRIM(DOMVIOL))) = 0 THEN NULL
		ELSE DOMVIOL
		END,
	--ABD_CHILD_AOD,
	CASE
		WHEN LEN(LTRIM(RTRIM(CHILDAODAB))) = 0 THEN NULL
		ELSE CHILDAODAB
		END,
	--ABD_HIVAIDS,
	CASE
		WHEN LEN(LTRIM(RTRIM(HIVAIDS))) = 0 THEN NULL
		ELSE HIVAIDS
		END,
	--ABD_SUICIDAL,
	CASE
		WHEN LEN(LTRIM(RTRIM(SUICIDAL))) = 0 THEN NULL
		ELSE SUICIDAL
		END,
	--ABD_SCHOOL_DO,
	CASE
		WHEN LEN(LTRIM(RTRIM(SCHOOLDO))) = 0 THEN NULL
		ELSE SCHOOLDO
		END,
	--ABD_PROBPAROLE,
	CASE
		WHEN LEN(LTRIM(RTRIM(PROBPAROLE))) = 0 THEN NULL
		ELSE PROBPAROLE
		END,
	--ABD_NONE,
	CASE
		WHEN LEN(LTRIM(RTRIM(NONE))) = 0 THEN NULL
		ELSE NONE
		END,
	--ABD_PREG,
	CASE
		WHEN LEN(LTRIM(RTRIM(PREG))) = 0 THEN NULL
		ELSE PREG
		END,
	--ABD_PREMED_HMO,
	CASE
		WHEN LEN(LTRIM(RTRIM(PREMEDHMO))) = 0 THEN NULL
		ELSE PREMEDHMO
		END,
	--ABD_HAS_PCP,
	CASE
		WHEN LEN(LTRIM(RTRIM(PCPHY))) = 0 THEN NULL
		ELSE PCPHY
		END,
	--ABD_REF_ASSESS,
	CASE
		WHEN LEN(LTRIM(RTRIM(REFASSESS))) = 0 THEN NULL
		ELSE REFASSESS
		END,
	--ABD_BIRTH_TYPE,
	CASE
		WHEN LEN(LTRIM(RTRIM(BIRTHTYPE))) = 0 THEN NULL
		ELSE BIRTHTYPE
		END,
	--ABD_URINALYSIS,
	CASE
		WHEN LEN(LTRIM(RTRIM(URINALYSIS))) = 0 THEN NULL
		ELSE URINALYSIS
		END,
	--ABD_MH_HIS,
	CASE
		WHEN LEN(LTRIM(RTRIM(MHHX))) = 0 THEN NULL
		ELSE MHHX
		END,
	--ABD_INTOX_AGE,
	CASE
		WHEN LEN(LTRIM(RTRIM(AGEFIRALCO))) = 0 THEN NULL
		WHEN ISNUMERIC(AGEFIRALCO) = 1 THEN CAST(AGEFIRALCO AS NUMERIC)
		ELSE NULL
		END,
	--ABD_NO_ARREST,
	CASE
		WHEN LEN(LTRIM(RTRIM(NOARREST))) = 0 THEN NULL
		WHEN ISNUMERIC(NOARREST) = 1 THEN CAST(NOARREST AS NUMERIC)
		ELSE NULL
		END,
	--ABD_PRIM_DRUG_CD,
	CASE
		WHEN LEN(LTRIM(RTRIM(PRIMDRUG))) = 0 THEN NULL
		ELSE PRIMDRUG
		END,
	--ABD_PRIM_FREQ,
	CASE
		WHEN LEN(LTRIM(RTRIM(PRIMFREQ))) = 0 THEN NULL
		WHEN ISNUMERIC(PRIMFREQ) = 1 THEN CAST(PRIMFREQ AS NUMERIC)
		ELSE NULL
		END,
	--ABD_PRIM_ROUTE,
	CASE
		WHEN LEN(LTRIM(RTRIM(PRIMROUTE))) = 0 THEN NULL
		ELSE PRIMROUTE
		END,
	--ABD_PRIM_FIRST_YR,
	CASE
		WHEN LEN(LTRIM(RTRIM(FIRSTYRUSE))) = 0 THEN NULL
		ELSE FIRSTYRUSE
		END,
	--ABD_SEC_DRUG_CD,
	CASE
		WHEN LEN(LTRIM(RTRIM(SECDRUG))) = 0 THEN NULL
		ELSE SECDRUG
		END,
	--ABD_SEC_FREQ,
	CASE
		WHEN LEN(LTRIM(RTRIM(SECFREQ))) = 0 THEN NULL
		WHEN ISNUMERIC(SECFREQ) = 1 THEN CAST(SECFREQ AS NUMERIC)
		ELSE NULL
		END,
	--ABD_SEC_ROUTE,
	CASE
		WHEN LEN(LTRIM(RTRIM(SECROUTE))) = 0 THEN NULL
		ELSE SECROUTE
		END,
	--ABD_SEC_FIRST_YR,
	CASE
		WHEN LEN(LTRIM(RTRIM(SECYRUSE))) = 0 THEN NULL
		ELSE SECYRUSE
		END,
	--ABD_TER_DRUG_CD,
	CASE
		WHEN LEN(LTRIM(RTRIM(TERDRUG))) = 0 THEN NULL
		ELSE TERDRUG
		END,
	--ABD_TER_FREQ,
	CASE
		WHEN LEN(LTRIM(RTRIM(TERFREQ))) = 0 THEN NULL
		WHEN ISNUMERIC(TERFREQ) = 1 THEN CAST(TERFREQ AS NUMERIC)
		ELSE NULL
		END,
	--ABD_TER_ROUTE,
	CASE
		WHEN LEN(LTRIM(RTRIM(TERROUTE))) = 0 THEN NULL
		ELSE TERROUTE
		END,
	--ABD_TER_FIRST_YR,
	CASE
		WHEN LEN(LTRIM(RTRIM(TERYRUSE))) = 0 THEN NULL
		ELSE TERYRUSE
		END,
	--ABD_HOSPADM,
	CASE
		WHEN LEN(LTRIM(RTRIM(HOSPADM))) = 0 THEN NULL
		WHEN ISNUMERIC(HOSPADM) = 1 THEN HOSPADM
		ELSE NULL
		END,
	--ABD_ERADM,
	CASE
		WHEN LEN(LTRIM(RTRIM(ERADM))) = 0 THEN NULL
		WHEN ISNUMERIC(ERADM) = 1 THEN ERADM
		ELSE NULL
		END,
	--ABD_REG_TO_PHY,
	CASE
		WHEN LEN(LTRIM(RTRIM(REGTOPHY))) = 0 THEN NULL
		WHEN ISNUMERIC(REGTOPHY) = 1 THEN REGTOPHY
		ELSE NULL
		END,
	--ABD_REG_TO_DEN,
	CASE
		WHEN LEN(LTRIM(RTRIM(REGTODEN))) = 0 THEN NULL
		WHEN ISNUMERIC(REGTODEN) = 1 THEN REGTODEN
		ELSE NULL
		END,
	--ABD_OPHC_VIS,
	CASE
		WHEN LEN(LTRIM(RTRIM(OPHCVIS))) = 0 THEN NULL
		WHEN ISNUMERIC(OPHCVIS) = 1 THEN OPHCVIS
		ELSE NULL
		END,
	--ABD_REIMB_SELF,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMSELF))) = 0 THEN NULL
		ELSE REIMSELF
		END,
	--ABD_REIMB_BCBS,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMBCBS))) = 0 THEN NULL
		ELSE REIMBCBS
		END,
	--ABD_REIMB_PRIV,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMBPRIV))) = 0 THEN NULL
		ELSE REIMBPRIV
		END,
	--ABD_REIMB_HMO,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMBHMO))) = 0 THEN NULL
		ELSE REIMBHMO
		END,
	--ABD_REIMB_OTH_PRIV,
	CASE
		WHEN LEN(LTRIM(RTRIM(REBOTHPRIV))) = 0 THEN NULL
		ELSE REBOTHPRIV
		END,
	--ABD_REIMB_COURT,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMCOURT))) = 0 THEN NULL
		ELSE REIMCOURT
		END,
	--ABD_REIMB_WORK,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMWORK))) = 0 THEN NULL
		ELSE REIMWORK
		END,
	--ABD_REIMB_REHAB,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMREHAB))) = 0 THEN NULL
		ELSE REIMREHAB
		END,
	--ABD_REIMB_EMP,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMBEMP))) = 0 THEN NULL
		ELSE REIMBEMP
		END,
	--ABD_MCR,
	CASE
		WHEN LEN(LTRIM(RTRIM(MEDICARE))) = 0 THEN NULL
		ELSE MEDICARE
		END,
	--ABD_MCD,
	CASE
		WHEN LEN(LTRIM(RTRIM(MEDICAID))) = 0 THEN NULL
		ELSE MEDICAID
		END,
	--ABD_TITLEXX,
	CASE
		WHEN LEN(LTRIM(RTRIM(TITLEXX))) = 0 THEN NULL
		ELSE TITLEXX
		END,
	--ABD_REIMB_FOREN,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMFOREN))) = 0 THEN NULL
		ELSE REIMFOREN
		END,
	--ABD_REIMB_OTHGOV,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMOTHGOV))) = 0 THEN NULL
		ELSE REIMOTHGOV
		END,
	--ABD_REIMB_OTHPAY,
	CASE
		WHEN LEN(LTRIM(RTRIM(REIMOTHPAY))) = 0 THEN NULL
		ELSE REIMOTHPAY
		END,
	--ABD_NOCHARGE,
	CASE
		WHEN LEN(LTRIM(RTRIM(NOCHARGE))) = 0 THEN NULL
		ELSE NOCHARGE
		END,
	--ABD_INSEXHAUS,
	CASE
		WHEN LEN(LTRIM(RTRIM(INSEXHAUS))) = 0 THEN NULL
		ELSE INSEXHAUS
		END,
	--ABD_DISPUTED,
	CASE
		WHEN LEN(LTRIM(RTRIM(DISPUTED))) = 0 THEN NULL
		ELSE DISPUTED
		END,
	--ABD_NONCOOP,
	CASE
		WHEN LEN(LTRIM(RTRIM(NONCOOP))) = 0 THEN NULL
		ELSE NONCOOP
		END,
	--ABD_NORESINSCO,
	CASE
		WHEN LEN(LTRIM(RTRIM(NORESINSCO))) = 0 THEN NULL
		ELSE NORESINSCO
		END,
	--ABD_NOTCOV,
	CASE
		WHEN LEN(LTRIM(RTRIM(NOTCOV))) = 0 THEN NULL
		ELSE NOTCOV
		END,
	--ABD_BDCOV,
	CASE
		WHEN LEN(LTRIM(RTRIM(BDCOV))) = 0 THEN NULL
		ELSE BDCOV
		END,
	--ABD_DOS_REQ,
	CASE
		WHEN LEN(DOSREQ) = 0 THEN NULL
		WHEN ISDATE(DOSREQ) = 1 THEN CAST(DOSREQ AS DATETIME)
		ELSE NULL
		END,
	--ABD_VET_STAT,
	CASE
		WHEN LEN(LTRIM(RTRIM(VETSTAT))) = 0 THEN NULL
		ELSE VETSTAT
		END,
	--ABD_CHILD_CUST,
	CASE
		WHEN LEN(LTRIM(RTRIM(CHILDCUS))) = 0 THEN NULL
		ELSE CHILDCUS
		END,
	--ABD_LEGAL_STAT,
	CASE
		WHEN LEN(LTRIM(RTRIM(LEGALSTAT))) = 0 THEN NULL
		ELSE LEGALSTAT
		END,
	--ABD_SALARY,
	CASE
		WHEN LEN(INDINCOME) = 0 THEN NULL
		WHEN ISNUMERIC(INDINCOME) = 1 THEN CAST(INDINCOME AS NUMERIC)
		ELSE NULL
		END,
	--ABD_REFTOCD,
	CASE
		WHEN LEN(LTRIM(RTRIM(REFTOCODE))) = 0 THEN NULL
		ELSE REFTOCODE
		END,
	--ABD_REFTO_PROV,
	CASE
		WHEN LEN(LTRIM(RTRIM(REFTOUPID))) = 0 THEN NULL
		ELSE RIGHT('000000000000' + LTRIM(RTRIM(REFTOUPID)),12)
		END,
	--ABD_DT_SENT,
	CASE
		WHEN LEN(DTPROVSENT) = 0 THEN NULL
		WHEN ISDATE(DTPROVSENT) = 1 THEN CAST(DTPROVSENT AS DATETIME)
		ELSE NULL
		END,
/*R*/	--ABD_BH_DATA_SYS,
	@DataSystemName,
/*R*/	--ABD_BH_DATA_SRC,
	@ExtractFileName,
/*S*/	--ABD_BH_INS
	GETDATE()
/*S*/	--ABD_BH_UPD
FROM	ABD_MI50_Source.dbo.STATE_BH
WHERE	RECTYPE='A'







 
check the datatypes of your table, maybe in the conversion something got changed. YOu are putting leading zeros onthe ids, so that may be where the error lies.

Do you know which field it is failing on? That would make it easier to figure out whta the problem is.

"NOTHING is more important in a database than integrity." ESquared
 
I can't wade through all that right now, but first of all, you can simplify all your statements like so:

Code:
    CASE
        WHEN LEN(LTRIM(RTRIM(IOPEPI))) = 0 THEN NULL
        WHEN ISNUMERIC(IOPEPI) = 1 THEN CAST(IOPEPI AS NUMERIC)
        ELSE NULL
        END

-->

CASE WHEN IsNumeric(IOPEPI) = 1 THEN Cast(IOPEPI AS numeric)
END
You can tack on the ELSE NULL if you want, but it's not required. If this gives an error, then add a WHEN IOPEPI = '' THEN NULL as the first when expression. (That's equivalent to what you're doing with your Len function.)

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]
 
Thanks for the quick response. The error indicates it is at the truncate table line. This does not seem to make sense.

I did check the tables to make sure the datatypes did not change. The source table is all varchar and the destination table is correct.
 
what is the structure for the destination table?

"NOTHING is more important in a database than integrity." ESquared
 
Denis, good point, I completely missed that with my focus on the overcomplicated case statement.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top