SQL: How to resolve varchar numeric conversion using OUTER APPLY and SELECT TOP (1)?

Question

I have a base table with invoices for different stock codes, the important fields are:

StockCode	InvoiceDate
AAA	2022-01-01
AAA	2022-05-01
BBB	2022-02-01
BBB	2022-11-01

I am trying to work back to the cost price of those items using an amendment journal which records the changes to cost of the material. The amendment journal looks like this:

StockCode	JnlDate	Before	After
AAA	2022-02-01	10.000	11.000
AAA	2022-06-01	11.000	12.000
BBB	2022-03-01	20.000	21.000
BBB	2022-11-02	21.000	22.000

What I would like to do is to use OUTER APPLY to get the first amendment that occurs after the sale and get the [Before] price - which would be the price at that time. If there are no amendments after the invoice, I am able to make use of its current material cost on the system using ISNULL on the joined [Before] value.

My current code looks like this:

WITH
allChanges AS 
(
-- Need to convert the varchar to numeric in the amendment journal
SELECT StockCode, JnlDate, 
        CONVERT(DECIMAL(18,6),REPLACE([Before],',','')) AS [Before], 
        CONVERT(DECIMAL(18,6),REPLACE([After],',','')) AS [After]
FROM InvMastAmendJnl 
WHERE CostType = 'MaterialCost'
),
invoices AS 
(
SELECT Invoice, StockCode, InvoiceDate FROM InvoiceTable
)
-- Begin query
SELECT Invoice, StockCode, InvoiceDate
FROM invoices
OUTER APPLY
(
SELECT TOP(1) [Before] AS MaterialCost
FROM allChanges
WHERE allChanges.StockCode = invoices.StockCode AND allChanges.JnlDate > invoices.InvoiceDate
ORDER BY JnlDate ASC
)

Important points:

• When I execute each temporary tables (allChanges and invoices) individually there are no conversion errors.
• When I run the code above the following error occurs: "Error converting data type varchar to numeric."
• If I remove the TOP(1) and ORDER BY statements, there are no errors, but i get all the amendments which occured after the sales date being joined to the table.
• If I limit the full query to a single stock code, and leave TOP(1) and ORDER BY, e.g. WHERE StockCode = 'AAA', then there also isn't any conversion error.

Ultimately, what I want is a single value to return and be joined to the base table, so I need to get a single value from the outer apply for each row - what should I do?

Answer 1

The reason that this was occurring was because of the execution plan. There is other data in the amendment journal which is not numbers and that was causing the error, despite the where clause in the cte which is only meant to get the MaterialCost.

The solution: Use a Row Goal I added the following:

SELECT TOP (1000000) StockCode, JnlDate, 
        CONVERT(DECIMAL(18,6),REPLACE([Before],',','')) AS [Before], 
        CONVERT(DECIMAL(18,6),REPLACE([After],',','')) AS [After]
FROM InvMastAmendJnl 
WHERE CostType = 'MaterialCost'