locked
Data Conversion Issue RRS feed

  • Question

  • User-1738841732 posted

    My below code works fine, where i get the colum(Payment1 and Payment2) values from [TEMP_PAYMENT_TABLE] to MY_TABLE.
    When i add the add third colum(Payment_Due) that does substract operation(Column1 "MINUS" Colum2)
    I get the below error
    Conversion failed when converting the nvarchar value '0.0000' to data type int.
    Here the are few null values also in colum 1 and colum2

    WITH mycte AS (
    SELECT [PROCESS]
    ,TRY_CAST([LOAD_XML] AS XML) bond
    FROM [TEMP_PAYMENT_TABLE]
    )

    INSERT INTO [dbo].[MY_TABLE]
    (,[PAYMENT_VALUE1]
    ,[PAYMENT_VALUE2]
    --,[PAYMENT_DUE]

    SELECT
    S.a.value('(Compamy/Payment1/text())[1]', 'nvarchar(255)') Payment1
    ,S.a.value('(Compamy/Payment2/text())[1]', 'nvarchar(255)') Payment2

    --,S.a.value('(Compamy/Payment1/text())[1]', 'nvarchar(255)')-S.a.value('(Compamy/Payment2/text())[1]', 'nvarchar(255)')


    FROM mycte t

    CROSS APPLY t.Payment_xmlload.nodes('/.') S(a)


    --select * from [dbo].[MY_TABLE]

    Thursday, May 14, 2020 4:08 PM

Answers

  • User452040443 posted

    Hi,

    Try something like this:

    WITH 
        mycte AS 
        (
            SELECT 
                 [PROCESS]
                ,TRY_CAST([LOAD_XML] AS XML) Payment_xmlload
            FROM [TEMP_PAYMENT_TABLE]
        ),
    
        mycte_pay as
        (
            SELECT
                S.a.value('(Compamy/Payment1/text())[1]', 'decimal(19, 4)') Payment1
               ,S.a.value('(Compamy/Payment2/text())[1]', 'decimal(19, 4)') Payment2
            FROM mycte t
            CROSS APPLY t.Payment_xmlload.nodes('/.') S(a)    
        )
    
    INSERT INTO [dbo].[MY_TABLE]
    ([PAYMENT_VALUE1], [PAYMENT_VALUE2], [PAYMENT_DUE])
    
        select Payment1, Payment2, Payment1 - Payment2
        from mycte_pay
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 14, 2020 6:03 PM