@Product I have a use case where I need to cast a particular value as text.
I'm generating a JSON array from the "Output JSON" of "Query a CSV using SQL". This is being used in my final payload to the R365 API to create an invoice.
The R365 API has strict type validation and specifically requires the vendorItemNumber field to be formatted as a string. In other examples, this query worked fine because there were alphanumeric values for the vendorItemNumber. In an example from the weekend, there was one item and it was purely numerical.
See WA-103 here: https://account.wrk.com/v2/dashboard?client_id=5923&wrkflow=61014&panel=launches&launch=1768092
The "Query" returned vendorItemNumber as a number in the JSON output, which then caused an unsuccessful on the "Perform an API call".
I tried to use the cast key word using the WA testing, but it seems to still return the value as a number in the JSON output. I tried these two queries:
Query 1:
SELECT
comment,
quantity,
totalAmount,
CAST(itemCode AS TEXT) AS vendorItemNumber
FROM csv
ORDER BY SrNo ASC
Query 2:
SELECT
comment,
quantity,
totalAmount,
itemCode || '' AS vendorItemNumber
FROM csv
ORDER BY SrNo ASC
In both cases, the JSON response had the vendorItemNumber as a number without quotation marks. Is this expected? What is the recommended workaround if this is expected?
