I have a table defined as follows:
create table WebUIUpgrade.AuditInvoiceLineCountsBeforeFinalSalesTaxLineWrites
(InvoiceNumber_TIHLNUMINV numeric(10,0) not null,
TotalRestoreSourceLineCount int not null,
InsertedDatetime datetime not null,
countIsDifferent bit not null
I want to (1) define the TotalTargetLineCount as the sum of the SumTaxLineCount + SumNonTaxLineCount, (2) compare the TotalRestoreSourceLineCount to the value of the defined TotalTargetLineCount, (3) set the countIsDifferent bit to 1 if TotalRestoreSourceLineCount and the TotalTartetLineCount don't match.
To do this, I used this SQL statement:
select sum(SumNonTaxLineCount + SumTaxLineCount) as TotalTargetLineCount,
when (TotalRestoreSourceLineCount - sum(SumNonTaxLineCount + SumTaxLineCount)) > 0 then '1'
end as countIsDifferent
group by InvoiceNumber_TIHLNUMINV
The statement comes back with the error:
Msg 102, Level 15, State 1, Line 742
Incorrect syntax near '>'.
Any ideas on how to solve this?