Arithmetic overflow error converting expression to data type nvarchar.

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4447

    Is there a way to find the field this error is being generated by? I am trying to bring data from an AS400 to SQL.  I am attempting to convert 3 fields to date formats. I have a staging table that converts them to nvarchar(10) then when inserting into the final table I do IDATE(field) = 1 then I convert to date else I insert null. if I comment out the date fields it still throws that error. I have increased the size of all the fields, most were decimal, char and numeric. I will post the code of original table and staging table.

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4447

    Original Table from AS400
    SELECT
    NOUSGE, /* NOUSGE CHARACTER(1) DO NOT USE FLAG */
    MVEND, /* MVEND CHARACTER(7) Vendor Number */
    SPACE2, /* SPACE2 CHARACTER(2) * */
    MNAME, /* MNAME CHARACTER(30) Vendor Name */
    MADDR1, /* MADDR1 CHARACTER(30) Acct Addr Line 1 */
    MADDR2, /* MADDR2 CHARACTER(30) Acct Addr Line 2 */
    MCITY, /* MCITY CHARACTER(15) Acct City */
    MSTATE, /* MSTATE CHARACTER(2) Acct State */
    MZIP, /* MZIP CHARACTER(9) Acct Zip */
    MAGENT, /* MAGENT CHARACTER(2) Purchsng Agent # */
    M1099C, /* M1099C CHARACTER(1) 1099 Code */
    LCVCDE, /* LCVCDE CHARACTER(1) L/C Vendor Code Y/N */
    MPHONE, /* MPHONE NUMERIC(10,0) Acct Phone # */
    MCNTCT, /* MCNTCT CHARACTER(20) Acct Contact Person */
    MSHORT, /* MSHORT CHARACTER(10) Short Name */
    OS#VN1, /* OS#VN1 NUMERIC(3,0) OffSite Asmb Shipto# */
    MTMCOD, /* MTMCOD CHARACTER(2) Terms Code */
    M1099I, /* M1099I CHARACTER(15) 1099 ID # */
    MFAX, /* MFAX NUMERIC(10,0) Acct Fax # */
    MDIRCT, /* MDIRCT CHARACTER(1) Ship Direct */
    MN$VND, /* MN$VND NUMERIC(9,2) Min $ Order Amt */
    CP$VND, /* CP$VND NUMERIC(9,2) Coop. $ Agreement */
    MFRGHT, /* MFRGHT CHARACTER(1) Freight */
    MP$YTD, /* MP$YTD DECIMAL(11,2) Purchases YTD */
    MDTYTD, /* MDTYTD DECIMAL(9,2) Discnt Taken YTD */
    MDLYTD, /* MDLYTD DECIMAL(9,2) Discnt Lost YTD */
    MP$YLR, /* MP$YLR DECIMAL(11,2) Purchases Last Year */
    MDTLYR, /* MDTLYR DECIMAL(9,2) Disc Taken Last Year */
    MDLLYR, /* MDLLYR DECIMAL(9,2) Disc Lost Last Year */
    MDELET, /* MDELET CHARACTER(1) Dlt Cd */
    SPACB1, /* SPACB1 CHARACTER(1) * */
    ATCVND, /* ATCVND CHARACTER(1) Buyer Attn Cd */
    SPACC1, /* SPACC1 CHARACTER(1) * */
    MLACTD, /* MLACTD NUMERIC(8,0) Last Actvty Date */
    MLPMTD, /* MLPMTD NUMERIC(8,0) Last Pymnt Date */
    CTLMTD, /* CTLMTD NUMERIC(10,0) CSR Phone # */
    CFXMTD, /* CFXMTD NUMERIC(10,0) CSR FAx # */
    CNTMTD, /* CNTMTD CHARACTER(20) CSR Contact */
    LCPMTD, /* LCPMTD NUMERIC(5,0) L/C Cost% */
    OSAVND, /* OSAVND CHARACTER(1) Offsite Asmb Vendor */
    CARVND, /* CARVND CHARACTER(13) Freight Carrier */
    CM1VND, /* CM1VND CHARACTER(30) PO Default Comment1 */
    CM2VND, /* CM2VND CHARACTER(30) PO Default Comment2 */
    FRTVND, /* FRTVND CHARACTER(23) Vendor Freight Terms */
    DPTMND, /* DPTMND NUMERIC(3,0) PO Default Dept# */
    OS#VN2, /* OS#VN2 NUMERIC(4,0) OffSite Asmb ShipTo# */
    LDTVND, /* LDTVND NUMERIC(3,0) Vendor Lead- Time Days */
    MNOVND, /* MNOVND NUMERIC(7,0) Minimum Order Qty */
    MNUVND, /* MNUVND CHARACTER(2) Min Ordr U of M */
    EMLVND, /* EMLVND CHARACTER(60) A/P Email Address */
    ADTVND, /* ADTVND NUMERIC(8,0) Vendor Add Date */
    CMTVND, /* CMTVND CHARACTER(60) Vendor Comment */
    CMLVND, /* CMLVND CHARACTER(40) CSR Email Address */
    RTLVND, /* RTLVND NUMERIC(10,0) SalesRep Phone # */
    RFXVND, /* RFXVND NUMERIC(10,0) SalesRep Fax # */
    RNTVND, /* RNTVND CHARACTER(20) SalesRep Contact */
    RMLVND, /* RMLVND CHARACTER(40) SalesRep Email Addrs */
    MA1VND, /* MA1VND CHARACTER(30) Mailing Address #1 */
    MA2VND, /* MA2VND CHARACTER(30) Mailing Address #2 */
    MCYVND, /* MCYVND CHARACTER(15) Mailing City */
    MSTVND, /* MSTVND CHARACTER(2) Mail State */
    MZPVND, /* MZPVND CHARACTER(9) Mail Zip Code */
    VDSVND, /* VDSVND NUMERIC(4,4) VENDOR DSCNT PCNT */
    BX#VND, /* BX#VND NUMERIC(1,0) 1099 BOX# */
    BIPVND, /* BIPVND CHARACTER(1) BUYER INITIATED PAYMNT */
    SPA251 /* SPA251 CHARACTER(251) * */
    FROM QS36F.TABLE99;

    To TABLE IN SQL
    USE [QS36f]
    GO

    /****** Object: Table [dbo].[A99MAST] Script Date: 3/31/2020 2:40:18 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[A99MAST](
    [DO NOT USE FLAG] [nvarchar](2) NULL,
    [VENDOR NUMBER] [nvarchar](12) NULL,
    [SPACE2] [nvarchar](12) NULL,
    [VENDOR NAME] [nvarchar](50) NULL,
    [ACCT ADDRESS LINE 1] [nvarchar](50) NULL,
    [ACCT ADDRESS LINE 2] [nvarchar](50) NULL,
    [ACCOUNT CITY] [nvarchar](25) NULL,
    [ACCOUNT STATE] [nvarchar](4) NULL,
    [ACCOUNT ZIP] [nvarchar](10) NULL,
    [PURCHASING AGENT] [nvarchar](4) NULL,
    [1099 CODE] [nvarchar](10) NULL,
    [L/C VENDOR CODE Y/N] [nvarchar](2) NULL,
    [ACCOUNT PHONE] [nvarchar](15) NULL,
    [ACCOUNT CONTACT PERSON] [nvarchar](50) NULL,
    [SHORT NAME] [nvarchar](50) NULL,
    [OFFSITE ASSEM SHIPTO1] [nvarchar](6) NULL,
    [TERMS CODE] [nvarchar](6) NULL,
    [1099 ID] [nvarchar](15) NULL,
    [ACCOUNT FAX] [nvarchar](15) NULL,
    [SHIP DIRECT] [nvarchar](4) NULL,
    [MIN ORDER AMOUNT] [nvarchar](16) NULL,
    [COOP. AGREEMENT] [nvarchar](16) NULL,
    [FREIGHT] [nvarchar](4) NULL,
    [PURCHASES YTD] [nvarchar](16) NULL,
    [DISCOUNT TAKEN YTD] [nvarchar](16) NULL,
    [DISCOUNT LOST YTD] [nvarchar](16) NULL,
    [PURCHASES LAST YR] [nvarchar](16) NULL,
    [DISCOUNT TAKEN LAST YR] [nvarchar](16) NULL,
    [DISCOUNT LOST LAST YR] [nvarchar](16) NULL,
    [DLT CD] [nvarchar](4) NULL,
    [SPACB1] [nvarchar](15) NULL,
    [BUYER ATTN CD] [nvarchar](15) NULL,
    [SPACC1] [nvarchar](25) NULL,
    [LAST ACTIVITY DATE] [nvarchar](16) NULL,
    [LAST PAYMENT DATE] [nvarchar](16) NULL,
    [CSR PHONE] [nvarchar](15) NULL,
    [CSR FAX] [nvarchar](15) NULL,
    [CSR CONTACT] [nvarchar](50) NULL,
    [L/C COST PERCENT] [nvarchar](15) NULL,
    [OFFSITE ASMB VENDOR] [nvarchar](6) NULL,
    [FREIGHT CARRIER] [nvarchar](50) NULL,
    [PO DEFAULT COMMENTS1] [nvarchar](255) NULL,
    [PO DEFAULT COMMENTS2] [nvarchar](255) NULL,
    [VENDOR FREIGHT TERMS] [nvarchar](30) NULL,
    [PO DEFAULT DEPT] [nvarchar](15) NULL,
    [OFFSITE ASMB SHIPTO] [nvarchar](15) NULL,
    [VENDOR LEAD-TIME DAYS] [nvarchar](6) NULL,
    [MINIMUM ORDER QTY] [nvarchar](6) NULL,
    [MIN ORDER UOM] [nvarchar](6) NULL,
    [A/P EMAIL ADDRESS] [nvarchar](75) NULL,
    [VENDOR ADD DATE] [nvarchar](15) NULL,
    [VENDOR COMMENTS] [nvarchar](255) NULL,
    [CSR EMAIL ADDRESS] [nvarchar](40) NULL,
    [SALESREP PHONE] [nvarchar](15) NULL,
    [SALESREP FAX] [nvarchar](15) NULL,
    [SALESREP CONTACT] [nvarchar](50) NULL,
    [SALESREP EMAIL ADDRESS] [nvarchar](50) NULL,
    [MAILING ADDRESS1] [nvarchar](50) NULL,
    [MAILING ADDRESS2] [nvarchar](50) NULL,
    [MAILING CITY] [nvarchar](50) NULL,
    [MAILING STATE] [nvarchar](25) NULL,
    [MAILING ZIP] [nvarchar](9) NULL,
    [VENDOR DSCNT PCNT] [nvarchar](15) NULL,
    [1099 BOX] [nvarchar](6) NULL,
    [BUYER INITIATED PAYMNT] [nvarchar](255) NULL,
    [SPA251] [nvarchar](255) NULL
    ) ON [PRIMARY]
    GO


  • pietlinden

    SSC Guru

    Points: 62780

    Is your data really double-byte (from double-byte languages like Arabic and Japanese?) If not, just use VARCHAR instead.

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4447

    No it isn't and youre talking about the decimal fields correct?

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4447

    Here is some sample data, I have removed any data that had identifying information, phone numbers and faxs and names and addresses

    Attachments:
    You must be logged in to view attached files.
  • pietlinden

    SSC Guru

    Points: 62780

    NVARCHAR() fields are text.

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4447

    Yes they are, no calculations will be done on them.

  • frederico_fonseca

    SSChampion

    Points: 14651

    few comments on this

    1. do not use nvarchar unless you have double byte data - use varchar instead
    2. do not use names that require use of [] around them - remove the spaces and drop special chars like /
    3.  use clear names
    4. if a field is not used on AS400 do not create it on SQL - that would most likely be the case of the "do not use" and the "filler2"

    Date fields - they look like numbers representing a date on format DDMMYYYY but you need to speak with the owner of the software on the AS400 to confirm this and to see if they have values representing particular dates which you may need to reproduce.

    to convert these dates you should do something like - right('0' + convert(varchar(8), datefield), 8) to get a string representation of the date and then use substrings on it to convert to format yyyymmdd for easy conversion to a date

    DO NOT use isdate - it will return true even for bad representations of a date - e.g. isdate('2007' will yield a valid date)

  • Chris Wooding

    SSCarpal Tunnel

    Points: 4329

    To find out which values are causing the problem, you could use TRY_CAST(YourColumn AS DATE). The values that can't be cast will be null in the new table. Then you can look at the same rows in the originating table to see what was in those columns. Note: this will still allow a few invalid dates through (as mentioned by Frederico regarding ISDATE above).

  • Jeff Moden

    SSC Guru

    Points: 996502

    I think you also find that the Date data coming from an AS400 or IBM PowerSystem will contain all 0's for "null" dates.

    Also, don't use VARCHAR(10) to hold string representations of dates.  You're just chewing up an additional 2 bytes (20%) per entry.  Use CHAR(10) instead and, like the other said, don't use NVARCHAR(10) for such things.

    And, seriously... using NVARCHAR(9) for a Zip Code column is just a crazy waste of byte space.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply