TSQL GETTING NULLS WHERE NONE SHOULD APPEAR
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.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.
Any help would be most appreciated. Thank you
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.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
It's almost certainly a column alignment problem.
Add a column list to the receiving table on the INSERT:
INSERT INTO [DMFBOMENTITY] ( col_name_1, .... )
SELECT ...
Add a column list to the receiving table on the INSERT:
INSERT INTO [DMFBOMENTITY] ( col_name_1, .... )
SELECT ...
ASKER
@Aneesh & @ScottPletcher I ran the select query (commented out the insert into line) and there are a few nulls showing up. When I go to the system I am pulling the data from it shows the to date fields (PSEXYR PSEXMO, PSEXDA) are populated with valid dates??
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:
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 bigintSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@scottplecher Ah ha! I am going to tweak my case statements to have an else statement to see and then give it a whirl.
ASKER
@ScottPlecher Imodified the code and now I no longer receive the null error, however a new error has occurred. I tried to enclose all of the TODATE case statements inside a CONVERT statement and received this error??
ERROR:
Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
ERROR:
Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
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]
Hmm, doesn't look too bad overall.
I've marked the only things I see with "--!!". Review those to see if those changes/ideas help.
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] ASKER
I modified the code and now have a new error. I am trying to figure out if there is a numeric field that has data larger than the field attribute.
ERROR:
Msg 8115, Level 16, State 6, Line 6
Arithmetic overflow error converting varchar to data type numeric.
The statement has been terminated.
Modified code:
ERROR:
Msg 8115, Level 16, State 6, Line 6
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Woah, why did you change the date components from strings to numbers? That will invalidate your date. I don't think that's the numeric problem you're having, but it will give you an invalid date value issue.
Constructing 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.
Constructing 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.
ASKER
@ScottPletcher, I finally went this route with both the FROMDATE & TODATE date strings:
,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.
,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.
ASKER
I find the experts on this site very very helpful and find Experts Exchange an excellent resource for those bumps in the road when dealing with technology specifically SQL coding in my case.
You can also try running the select query and see whether it is returning any NULLs on that column