Sample Header Ad - 728x90

JSON_Value problem when adding persisted Computed column

0 votes
1 answer
121 views
i want to add a persisted column and i get an error: Msg 8116, Level 16, State 8, Line 3 Argument data type NULL is invalid for argument 2 of JSON_QUERY function. here is my script:
ALTER TABLE [ProviderDataSync].[Messages]
ADD [account_id1] AS (json_value(json_query([Message],'$.'+json_value([Message],'$.requestType')),'$.accountId')) PERSISTED
i understand that it originates from the JSON_QUERY as i get the same result when running
SELECT  top 2000 
      json_query([Message],'$.'+json_value([Message],'$.requestType'))
  FROM [ProviderDataSync].[Messages]
it stops on line 1352. i tried to understand if it's some irregular data but i faile to see it. here is the line between 1351 and 1352
line 1351 : {"sourceId":39,"version":"1.2","apiId":"EA_44657857e4b5a","requestType":["cardStatusChange"],"sendingRetriesCount":0,"timestamp":1598441115,"cardAuthorization":null,"cardSettlement":null,"cardStatusChange":{"cardId":15614,"newStatus":"A","previousStatus":"N","initiatedBy":"api"},"externalPaymentSettlement":null,"directDebitMandate":null,"directDebitDue":null,"directCreditReceived":null,"financialDetailAddendum":null,"negativeAcknowledgement":null,"cardTokenization":null,"tokenUpdates":null,"epmAddressAssignCompleted":null,"epmAddressAssignFailed":null,"customNotification":null,"externalPaymentNotification":null}

line 1352:
{"sourceId":1,"version":1,"apiId":"1","tribeRequestType":["cardAuthorization"],"sendingRetriesCount":0,"cardAuthorization":{"cardId":123456,"accountId":12119,"availableBalance":99996157,"settledBalance":100000000,"transLink":"883244000000MCCA00021000021200602123493","transactionStatusCode":"A","statusCode":"00","responseCode":"00","entryModeType":0,"transactionType":2,"authorizeType":"AIM","transactionAmount":-22,"transactionCurrencyIson":"840","billingAmount":-22,"billingCurrencyIson":"840","holderAmount":-22,"holderCurrencyIson":"840","totalHolderAmount":-100,"currencyConvertionFee":0,"mti":"100","productId":1,"transactionCountryIson":"076","acquirerId":"019780","additionalAmount":{"accountType1":"00","currencyCode1":"826","creditDebitIndicator1":"D","amount1":"000000001000","accountType2":"00","currencyCode2":"826","creditDebitIndicator2":"D","amount2":"000000001000","original":"*****","raw":"*****"},"txAmountFee":0,"authCode":"883244","mccCode":"1520","merchantId":"SHP000000000017","merchantName":"mer11**AFS34373        London        GBR","posDataCode":"810","posData":"1025104006000076902101234","posTerminalId":"pa1bra","posTime":"200602132821","procCode":"000000","retrievalReferenceNumber":"060200000021","traceLogId":15911045028444,"transactionTime":1598441359,"additionalData1":"T3206123456610510000371502110000012345642070103210","authorizedByStandIn":"Y","cardUsageGroup":1,"institutionCode":15907405717567,"partialApproval":1,"suspicious":0,"notifyHolder":0,"riskActions":{"markTransactionAsSuspicious":0,"notifyCardholderBySendingTaisNotification":0,"changeCardStatusToRisk":0,"changeAccountStatusToSuspended":0,"rejectTransaction":0},"securityChecks":{"cardExpirationDatePresent":0,"onlinePin":0,"offlinePin":0,"cvv2":0,"magneticStripe":1,"chipData":0,"avs":0,"phoneNumber":0},"validDateTo":"\/Date(1640988000000-0000)\/"},"cardSettlement":0,"cardStatusChange":0,"externalPaymentSettlement":0,"directDebitMandate":0,"directDebitDue":0,"directCreditReceived":0,"negativeAcknowledgement":0,"financialDetailAddendum":0,"cardTokenization":0,"tokenUpdates":0,"epmAddressAssignCompleted":0,"epmAddressAssignFailed":0,"customNotification":0}
how do i add this persisted column? what am i doing wrong? is it a problem with the data or the script?
Asked by dexon (65 rep)
Jan 30, 2024, 05:18 PM
Last activity: Feb 11, 2024, 11:03 AM