The following statement works in Oracle Database 23ai but produces a syntax error in the VS Code extension 24.3.1.
with
j_purchaseorder as (
select json(
'{
"PONumber": 1600,
"Reference": "ABULL-20140421",
"Requestor": "Alexis Bull",
"User": "ABULL",
"CostCenter": "A50",
"ShippingInstructions": {
"name": "Alexis Bull",
"Address": {
"street": "200 Sporting Green",
"city": "South San Francisco",
"state": "CA",
"zipCode": 99236,
"country": "United States of America"
},
"Phone": [
{ "type": "Office", "number": "909-555-7307" },
{ "type": "Mobile", "number": "415-555-1234" }
]
},
"Special Instructions": null,
"AllowPartialShipment": true,
"LineItems": [
{
"ItemNumber": 1,
"Part": { "Description": "One Magic Christmas", "UnitPrice": 19.95, "UPCCode": 13131092899 },
"Quantity": 9.0
},
{
"ItemNumber": 2,
"Part": { "Description": "Lethal Weapon", "UnitPrice": 19.95, "UPCCode": 85391628927 },
"Quantity": 5.0
}
]
}') as po_document
)
select json_transform(
po_document,
nested path '$.LineItems[*]' (
case
when '@?(@.Quantity < 5)' then
( -- No discount
set '@.TotalPrice' = path '@.Quantity * @.Part.UnitPrice'
)
when '@?(@.Quantity < 7)' then
( -- 10% discount
set '@.TotalPrice' = path '@.Quantity * @.Part.UnitPrice * 0.9',
set '@.TotalPrice' = path '@.Quantity * @.Part.UnitPrice * 0.9'
)
else
( -- 15% discount
set '@.TotalPrice' = path '@.Quantity * @.Part.UnitPrice * 0.85'
)
end
)
)
from j_purchaseorder;
Here's the screenshot showing the complete error message:
