Solved

TSQL GETTING NULLS WHERE NONE SHOULD APPEAR

Posted on 2014-09-29
12
166 Views
Last Modified: 2014-10-02
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.

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]  

 		

Open in new window


Any help would be most appreciated.  Thank you
0
Comment
Question by:Leogal
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Without the table structure it is hard to determine the position of the date column,.
You can also try running the  select query and see whether it is returning any NULLs on that column
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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 ...
0
 

Author Comment

by:Leogal
Comment Utility
@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:

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

Open in new window

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
Comment Utility
You have some CASE statements without an explicit ELSE.  The default ELSE value is to return NULL.  A single NULL value will, of course, cause the whole return value to be NULL, regardless of the other concatenated values.

 ,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
 

Author Comment

by:Leogal
Comment Utility
@scottplecher Ah ha! I am going to tweak my case statements to have an else statement to see and then give it a whirl.
0
 

Author Comment

by:Leogal
Comment Utility
@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.




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]  

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.

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]  

Open in new window

0
 

Author Comment

by:Leogal
Comment Utility
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:

-- 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]  

 		

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
Comment Utility
Hi, the source mainframe system provides numbers (but which you might see as strings due to file handling.
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

Open in new window


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

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 

Author Comment

by:Leogal
Comment Utility
@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.
0
 

Author Closing Comment

by:Leogal
Comment Utility
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now