locked
Data Conversion Issue RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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