Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I really need so help to understand what I am missing here. I am getting an error regarding nulls but there are not any nulls in the incoming data..

The error appears when I run the following code:

**Msg 515, Level 16, State 2, Line 5**

Cannot insert the value NULL into column 'TODATE', table 'DmStagingVD.dbo.DMFBOMENTITY '; column does not allow nulls. INSERT fails.

The statement has been terminated.

I have checked the incoming data and the incoming date fields are all numeric 2 position. I do not see what I am missing in my code to cause this error to occur.

Any help would be most appreciated. Thank you

The error appears when I run the following code:

Cannot insert the value NULL into column 'TODATE', table 'DmStagingVD.dbo.DMFBOMENT

The statement has been terminated.

I have checked the incoming data and the incoming date fields are all numeric 2 position. I do not see what I am missing in my code to cause this error to occur.

```
USE DmStagingVD
INSERT INTO [DMFBOMENTITY]
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,0 as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,0 as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,0 as [DENSITY]
,0 as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
,
case
when PSEFYR =0 then '19'
when PSEFCT = 0 then '19'
else '20'
end
+ case
when PSEFYR < 10 and PSEFYR <> 0 then '0' + Cast(PSEFYR as varchar(1))
when pSEFYR = 0 THEN '01'
else Cast(PSEFYR as varchar(2))
END
+ '-'
+ case
when LEN(PSEFMO) = 1 and PSEFMO <> 0 then '0' + Cast(PSEFMO as varchar(1))
when len(PSEFMO) = 2 then cast(PSEFMO as varchar(2))
when PSEFMO = 0 then '01'
END
+ '-'
+ case
when len(PSEFDA) = 1 and PSEFDA <> 0 then '0' + Cast(PSEFDA as varchar(1))
when len(PSEFDA) = 2 then Cast(PSEFDA as varchar(2))
when PSEFDA = 0 then '01'
end AS [FROMDATE]
,0 as [HEIGHT]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,0 as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,0 as [OPRNUM]
,' ' as [PDSBASEVALUE]
,0 as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,0 as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,0 as [ROUNDUPQTY]
,0 as [ROWID]
,0 as [SCRAPCONST]
,0 as [SCRAPVAR]
,
case
when PSEXYR = 0 then '19'
when PSEXYR = 99 then '21'
when PSEXCT = 1 THEN '20'
end
+ case
when PSEXYR = 0 THEN '01'
when PSEXYR < 10 and PSEFYR <> 99 then '0' + Cast(PSEXYR as varchar(1))
when pSEXYR = 99 THEN '99'
else Cast(PSEFYR as varchar(2))
END
+ '-'
+ case
when PSEXMO = 0 THEN '01'
when LEN(PSEXMO) = 1 THEN '0' + Cast(PSEXMO as varchar(1))
when len(PSEXMO) = 2 AND PSEXMO <> 99 then cast(PSEXMO as varchar(2))
when PSEXMO = 99 then '12'
END
+ '-'
+ case
when PSEXDA = 0 THEN '01'
when len(PSEXDA) = 1 then '0' + Cast(PSEXDA as varchar(1))
when len(PSEXDA) = 2 and PSEXDA <> 99 then Cast(PSEXDA as varchar(2))
when PSEXDA = 99 then '31'
end AS [TODATE]
,0 as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,0 as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
,imco
FROM [VD_GPL].[VKITMSTR ] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
where [IMCO] in(01,03,05,07)
order by [PSPMRN]
```

Any help would be most appreciated. Thank you

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Add a column list to the receiving table on the INSERT:

INSERT INTO [DMFBOMENTITY]

SELECT ...

I went back and validate that the columns are lining up between the script and the table and they appear to all be there in the same order.

Here is the file Layout:

```
BOM_APPROVED nvarchar(3)
BOM_APPROVERPARTYNAME nvarchar(100)
BOM_APPROVERPARTYNUMBER nvarchar(40)
BOM_APPROVERPERSONNELNUMBER nvarchar(25)
BOM_BOMID nvarchar(20)
BOM_CHECKBOM nvarchar(3)
BOM_ITEMGROUPID nvarchar(10)
BOM_NAME nvarchar(60)
BOM_ROUTE_CONFIGGROUPID nvarchar(10)
BOM_ROUTE_LINENUM numeric(32, 16)
BOM_ROUTE_NAME nvarchar(60)
BOMCONSUMP nvarchar(10)
BOMQTY numeric(32, 16)
BOMQTYSERIE numeric(32, 16)
BOMTYPE nvarchar(10)
CALCULATION nvarchar(10)
CONFIGGROUPID nvarchar(10)
CONFIGID nvarchar(10)
CONSTANT numeric(32, 16)
DEFINITIONGROUP nvarchar(60)
DENSITY numeric(32, 16)
DEPTH numeric(32, 16)
ENDSCHEDCONSUMP nvarchar(10)
EXECUTIONID nvarchar(90)
FORMULA nvarchar(10)
FROMDATE datetime
HEIGHT numeric(32, 16)
INVENTBATCHID nvarchar(20)
INVENTCOLORID nvarchar(10)
INVENTDIMID nvarchar(20)
INVENTLOCATIONID nvarchar(10)
INVENTSERIALID nvarchar(20)
INVENTSITEID nvarchar(10)
INVENTSIZEID nvarchar(10)
INVENTSTYLEID nvarchar(10)
ISSELECTED int
ITEMBOMID nvarchar(20)
ITEMID nvarchar(20)
ITEMPBAID nvarchar(20)
ITEMROUTEID nvarchar(20)
LINENUM numeric(32, 16)
OPRNUM int
PDSBASEVALUE nvarchar(30)
PDSCWQTY numeric(32, 16)
PDSINGREDIENTTYPE nvarchar(12)
PDSINHERITCOPRODUCTBATCHATTRIB nvarchar(3)
PDSINHERITCOPRODUCTSHELFLIFE nvarchar(3)
PDSINHERITENDITEMBATCHATTRIB nvarchar(3)
PDSINHERITENDITEMSHELFLIFE nvarchar(3)
PMFFORMULAPCT numeric(32, 16)
PMFPCTENABLE nvarchar(3)
PMFPLANGROUPID nvarchar(10)
PMFPLANGROUPPRIORITY int
PMFSCALABLE nvarchar(3)
POSITION nvarchar(30)
PRODFLUSHINGPRINCIP nvarchar(10)
PROJSETSUBPRODTOCONSUMED nvarchar(10)
ROUNDUP nvarchar(10)
ROUNDUPQTY numeric(32, 16)
ROWID int
SCRAPCONST numeric(32, 16)
SCRAPVAR numeric(32, 16)
TODATE datetime
TRANSFERSTATUS int
UNITID nvarchar(10)
VENDID nvarchar(20)
WIDTH numeric(32, 16)
WMSLOCATIONID nvarchar(10)
WMSPALLETID nvarchar(18)
WRKCTRCONSUMPTION nvarchar(10)
RECVERSION int
PARTITION bigint
RECID bigint
```

,0 as [SCRAPVAR]

,

case

when PSEXYR = 0 then '19'

when PSEXYR = 99 then '21'

when PSEXCT = 1 THEN '20'

end

+ case

when PSEXYR = 0 THEN '01'

when PSEXYR < 10 and PSEFYR <> 99 then '0' + Cast(PSEXYR as varchar(1))

when pSEXYR = 99 THEN '99'

else Cast(PSEFYR as varchar(2))

END

+ '-'

+ case

when PSEXMO = 0 THEN '01'

when LEN(PSEXMO) = 1 THEN '0' + Cast(PSEXMO as varchar(1))

when len(PSEXMO) = 2 AND PSEXMO <> 99 then cast(PSEXMO as varchar(2))

when PSEXMO = 99 then '12'

END

+ '-'

+ case

when PSEXDA = 0 THEN '01'

when len(PSEXDA) = 1 then '0' + Cast(PSEXDA as varchar(1))

when len(PSEXDA) = 2 and PSEXDA <> 99 then Cast(PSEXDA as varchar(2))

when PSEXDA = 99 then '31'

end AS [TODATE]

ERROR:

Msg 242, Level 16, State 3, Line 5

The statement has been terminated.

```
USE DmStagingVD
INSERT INTO [DMFBOMENTITY]
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,0 as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,0 as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,0 as [DENSITY]
,0 as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
,
case
when PSEFYR =0 then '19'
when PSEFCT = 0 then '19'
else '20'
end
+ case
when PSEFYR = NULL then '01'
when PSEFYR < 10 and PSEFYR <> 0 then '0' + Cast(PSEFYR as varchar(1))
when pSEFYR = 0 THEN '01'
else Cast(PSEFYR as varchar(2))
END
+ '-'
+ case
when PSEFMO = NULL then '01'
when LEN(PSEFMO) = 1 and PSEFMO <> 0 then '0' + Cast(PSEFMO as varchar(1))
when len(PSEFMO) = 2 then cast(PSEFMO as varchar(2))
when PSEFMO = 0 then '01'
END
+ '-'
+ case
when PSEFDA = NUll then '01'
when len(PSEFDA) = 1 and PSEFDA <> 0 then '0' + Cast(PSEFDA as varchar(1))
when len(PSEFDA) = 2 then Cast(PSEFDA as varchar(2))
when PSEFDA = 0 then '01'
end AS [FROMDATE]
,0 as [HEIGHT]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,0 as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,0 as [OPRNUM]
,' ' as [PDSBASEVALUE]
,0 as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,0 as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,0 as [ROUNDUPQTY]
,0 as [ROWID]
,0 as [SCRAPCONST]
,0 as [SCRAPVAR]
, convert(datetime,
case
when PSEXYR = 99 then '21'
when PSEXCT = 1 THEN '20'
else '19'
end
+ case
when PSEXYR = 0 THEN '01'
when PSEXYR < 10 and PSEFYR <> 99 then '0' + Cast(PSEXYR as varchar(1))
when pSEXYR = 99 THEN '99'
else Cast(PSEFYR as varchar(2))
END
+ '-'
+ case
when LEN(PSEXMO) = 1 THEN '0' + Cast(PSEXMO as varchar(1))
when len(PSEXMO) = 2 AND PSEXMO <> 99 then cast(PSEXMO as varchar(2))
when PSEXMO = 99 then '12'
else '01'
END
+ '-'
+ case
when len(PSEXDA) = 1 then '0' + Cast(PSEXDA as varchar(1))
when len(PSEXDA) = 2 and PSEXDA <> 99 then Cast(PSEXDA as varchar(2))
when PSEXDA = 99 then '31'
else '01'
end) AS [TODATE]
,0 as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,0 as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
,imco
FROM [VD_GPL].[VKITMSTR ] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
where [IMCO] in(01,03,05,07)
order by [PSPMRN]
```

I've marked the only things I see with "--!!". Review those to see if those changes/ideas help.

```
USE DmStagingVD
INSERT INTO [DMFBOMENTITY]
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,0 as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,0 as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,0 as [DENSITY]
,0 as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
,
case
when PSEFYR =0 then '19'
when PSEFCT = 0 then '19'
else '20'
end
+ case
when PSEFYR = NULL then '01'
when PSEFYR < 10 and PSEFYR <> 0 then '0' + Cast(PSEFYR as varchar(1))
when pSEFYR = 0 THEN '01'
else Right('0' + Cast(PSEFYR as varchar(2)), 2) --!!
END
+ '-'
+ case
when PSEFMO = NULL then '01'
when LEN(PSEFMO) = 1 and PSEFMO <> 0 then '0' + Cast(PSEFMO as varchar(1))
when len(PSEFMO) = 2 then cast(PSEFMO as varchar(2))
when PSEFMO = 0 then '01'
--can PSEFMO *ever* have a length of 0 or 3+? --!!
END
+ '-'
+ case
when PSEFDA = NUll then '01'
when len(PSEFDA) = 1 and PSEFDA <> 0 then '0' + Cast(PSEFDA as varchar(1))
when len(PSEFDA) = 2 then Cast(PSEFDA as varchar(2))
when PSEFDA = 0 then '01'
--can PSEFDA *ever* have a length of 0 or 3+? --!!
end AS [FROMDATE]
,0 as [HEIGHT]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,0 as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,0 as [OPRNUM]
,' ' as [PDSBASEVALUE]
,0 as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,0 as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,0 as [ROUNDUPQTY]
,0 as [ROWID]
,0 as [SCRAPCONST]
,0 as [SCRAPVAR]
, convert(datetime,
case
when PSEXYR = 99 then '21'
when PSEXCT = 1 THEN '20'
else '19'
end
+ case
when PSEXYR = 0 THEN '01'
when PSEXYR < 10 and PSEFYR <> 99 then '0' + Cast(PSEXYR as varchar(1))
when pSEXYR = 99 THEN '99'
else Right('0' + Cast(PSEFYR as varchar(2)), 2) --!!
END
+ '-'
+ case
when LEN(PSEXMO) = 1 THEN '0' + Cast(PSEXMO as varchar(1))
when len(PSEXMO) = 2 AND PSEXMO <> 99 then cast(PSEXMO as varchar(2))
when PSEXMO = 99 then '12'
else '01'
END
+ '-'
+ case
when len(PSEXDA) = 1 then '0' + Cast(PSEXDA as varchar(1))
when len(PSEXDA) = 2 and PSEXDA <> 99 then Cast(PSEXDA as varchar(2))
when PSEXDA = 99 then '31'
else '01'
end) AS [TODATE]
,0 as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,0 as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
,imco
FROM [VD_GPL].[VKITMSTR ] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
where [IMCO] in(01,03,05,07)
order by [PSPMRN]
```

ERROR:

Arithmetic overflow error converting varchar to data type numeric.

The statement has been terminated.

Modified code:

```
-- database to use
USE DmStagingVD
---- table the results write to
INSERT INTO [DMFBOMENTITY]
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,0 as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,0 as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,0 as [DENSITY]
,0 as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
,
case
when PSEFYR =0 then 19
when PSEFCT = 0 then 19
else 20
end
+ case
when PSEFYR = NULL then 01
when PSEFYR < 10 and PSEFYR <> 0 then 0 + Cast(PSEFYR as numeric(1))
when pSEFYR = 0 THEN 01
else Cast(PSEFYR as numeric(2))
END
+ '-'
+ case
when PSEFMO = NULL then 01
when LEN(PSEFMO) = 1 and PSEFMO <> 0 then 0 + Cast(PSEFMO as numeric(1))
when len(PSEFMO) = 2 then cast(PSEFMO as numeric(2))
when PSEFMO = 0 then 01
END
+ '-'
+ case
when PSEFDA = NUll then 01
when len(PSEFDA) = 1 and PSEFDA <> 0 then 0 + Cast(PSEFDA as numeric(1))
when len(PSEFDA) = 2 then Cast(PSEFDA as numeric(2))
when PSEFDA = 0 then 01
end AS [FROMDATE]
,0 as [HEIGHT]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,0 as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,0 as [OPRNUM]
,' ' as [PDSBASEVALUE]
,0 as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,0 as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,0 as [ROUNDUPQTY]
,0 as [ROWID]
,0 as [SCRAPCONST]
,0 as [SCRAPVAR]
,
case
when PSEXYR = 99 then 21
when PSEXCT = 1 THEN 20
else 19
end
+ case
when PSEXYR = 0 THEN 01
when PSEXYR < 10 and PSEFYR <> 99 then '0' + Cast(PSEXYR as numeric (1))
when pSEXYR = 99 THEN 99
else Cast(PSEFYR as numeric (2))
END
+ '-'
+ case
when LEN(PSEXMO) = 1 THEN '0' + Cast(PSEXMO as numeric (1))
when len(PSEXMO) = 2 AND PSEXMO <> 99 then cast(PSEXMO as numeric (2))
when PSEXMO = 99 then 12
else 01
END
+ '-'
+ case
when len(PSEXDA) = 1 then 0 + Cast(PSEXDA as numeric (1))
when len(PSEXDA) = 2 and PSEXDA <> 99 then Cast(PSEXDA as numeric (2))
when PSEXDA = 99 then 31
else 01
end AS [TODATE]
,0 as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,0 as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
,imco
FROM [VD_GPL].[VKITMSTR ] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
where [IMCO] in(01,03,05,07)
order by [PSPMRN]
```

see image: http://filedb.experts-exchange.com/incoming/2014/09_w39/873892/screen-print.png

I would still like to suggest the use of dateadd() for the calculation of your dates, I'm not sure why you accept that advice in one question but seem to revert back to string handling in the next question.

The arithmetic to arrive at valid dates doesn't seem overly complex and will be much simpler than doing it "by hand" through case expressions based on strings.

```
, CASE WHEN PSEXCT + PSEXDA + PSEXMO + PSEXYR = 0 THEN CAST('19000101' AS datetime)
WHEN PSEXDA = 99 AND PSEXMO = 99 AND PSEXYR = 99 THEN CAST('21991231' AS datetime)
ELSE DATEADD(DAY, (PSEXDA - 1), DATEADD(MONTH, (PSEXMO - 1), DATEADD(YEAR, (PSEXCT * 100) + PSEXYR, 0)))
END
```

Is there a problem adopting dateadd() that you need help with?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialConstructing dates the way you are, I would stick to string values, as the format 'YYYYMMDD' is always interpreted correctly by SQL, regardless of any date, language or other settings.

As to the numeric error, you need to identity all the numeric columns in the db and then check the values coming in to see if they are too large.

,CASE when PSEXMO between 1 and 12 then

substring(cast(20000000 + [PSEXYR]*10000 + [PSEXMO]*100 + [PSEXDA] as char(10) ),1,4)

+ '-'

+ substring(cast(20000000 + [PSEXYR]*10000 + [PSEXMO]*100 + [PSEXDA] as char(10) ),5,2)

+ '-'

+ substring(cast(20000000 + [PSEXYR]*10000 + [PSEXMO]*100 + [PSEXDA] as char(10) ),7,2)

else '1901-01-01'

end AS [TODATE]

@PortletPaul is not that your advice is not good, it is that I was not sure of myself and had a hard and fast deadline. I will go back and give this a whirl too.

Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

You can also try running the select query and see whether it is returning any NULLs on that column