I have the below query and subquery where I am getting a SUM value, and I want to join PS_VOUCHER_LINE LineSub conditionally (on Line.LINE_NBR = LineSub.LINE_NBR) on PS_VOUCHER Line only when a VOUCHER_ID has more than 1 LINE_NBR, otherwise I don't want this last join condition to execute. I am getting a Syntax error on the statement (Incorrect syntax near '='.) . How can I get this conditional join to work properly?
SELECT
CONCAT(Header.BUSINESS_UNIT, Header.VOUCHER_ID) AS INVOICE_ID
,
(
SELECT SUM(LineSub.MERCHANDISE_AMT)
FROM PS_VOUCHER_LINE LineSub
WHERE Line.BUSINESS_UNIT = LineSub.BUSINESS_UNIT
AND Line.VOUCHER_ID = LineSub.VOUCHER_ID
AND
CASE
WHEN COUNT(Line.LINE_NBR) > 1 THEN Line.LINE_NBR = LineSub.LINE_NBR
END
GROUP BY LineSub.VOUCHER_ID
) + Header.FREIGHT_AMT + Header.SALETX_AMT AS GROSS_AMT_LINE_FREIGHT_TAX
FROM
PS_VOUCHER Header
INNER JOIN PS_VOUCHER_LINE Line ON Line.BUSINESS_UNIT = Header.BUSINESS_UNIT
AND Line.VOUCHER_ID = Header.VOUCHER_ID
WHERE
Header.VOUCHER_ID = '00241107'