Dear community.
Good day.
I'm trying to use OpenJson in advance sql widget. I have the below data being passed to the input parameter:
N'[{ "RowNumber": 1, "PASName": "PAS123", "SourceName": "SourceA", "PolicyNumber": "POL001", "ProductCode": "PROD-X", "CountryCode": "US", "CoverageCode": "CVG01", "CoverageNumber": 1, "PlanId": "PLAN-A", "PlanName": "Plan Alpha", "BaseType": "PREM", "Cashflowstatus": "Active", "TransactionAmount": 1000.50, "EffDateOfTransaction": "2025-04-29T10:00:00", "ProcessDate": "2025-04-29T11:00:00", "AdviserId": "ADV001", "SalesId": "SALE001", "LicenseeId": "LIC001", "BusinessTaxClass": "BTX-A", "StatFundCode": "SF001", "CurrencyCode": "USD", "GST": 50.00, "CommissionRate": 0.05, "EarningsCode": "ERN01", "CommissionType": "COMM", "TaxRegime": "TR01", "PLannerType": "Financial", "Producttype": "Insurance", "NonPyableInd": "N" },{ "RowNumber": 2, "PASName": "PAS456", "SourceName": "SourceB", "PolicyNumber": "POL002", "ProductCode": "PROD-Y", "CountryCode": "CA", "CoverageCode": "CVG02", "CoverageNumber": 2, "PlanId": "PLAN-B", "PlanName": "Plan Beta", "BaseType": "PREM", "Cashflowstatus": "Active", "TransactionAmount": 2500.75, "EffDateOfTransaction": "2025-04-30T14:00:00", "ProcessDate": "2025-04-30T15:00:00", "AdviserId": "ADV002", "SalesId": "SALE002", "LicenseeId": "LIC002", "BusinessTaxClass": "BTX-B", "StatFundCode": "SF002", "CurrencyCode": "CAD", "GST": 125.00, "CommissionRate": 0.025, "EarningsCode": "ERN02", "CommissionType": "COMM", "TaxRegime": "TR02", "PLannerType": "Investment", "Producttype": "Mutual Fund", "NonPyableInd": "N" }]';
When I try to execute the sql in Sandbox, I am not receiving an error. But when the widget executes the sql, it throws an exception (not properly formatted).
Attached is the full script.
Thank you in advance for your help.
@Christopher Bautista : I did a demo using the JSON and query you have shared and it just worked. I have attached the OML for your reference.
Hi @Christopher Bautista , hope you’re doing well.
Please make sure you are using an i put parameter of type text.
Also in the Advance SQL do not wrap it in quotes or N’ ‘. If you use N’[ ]’ within SQL , server may interpret it incorrectly when Outsystems binds parameters.
Hope this helps.
Thanks and Regards!
Hi @Christopher Bautista,
You can also try,
Thank You.
Hi everyone,
Thank you all for your answers, it helped me isolate the issue. It turns out, there are json names of the attributes that were not matching vs. the ones within the "not exists" subquery and on how I formatted the json data.
Again, thank you, stay safe and more power to the community.
Regards,
Chris