Paula DiTallo
asked on
Why isn't this case statement working in MS SQL server?
Gurus,
I have a table defined as follows:
create table WebUIUpgrade.AuditInvoiceLineCountsBeforeFinalSalesTaxLineWrites
(InvoiceNumber_TIHLNUMINV numeric(10,0) not null,
TotalTargetLineCount int,
SumTaxLineCount int,
SumNonTaxLineCount int,
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,
InvoiceNumber_TIHLNUMINV,
case countIsDifferent
when (TotalRestoreSourceLineCount - sum(SumNonTaxLineCount + SumTaxLineCount)) > 0 then '1'
else
'0'
end as countIsDifferent
from WebUIUpgrade.AuditInvoiceLineCountsBeforeFinalSalesTaxLineWrites
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?
I have a table defined as follows:
create table WebUIUpgrade.AuditInvoiceLineCountsBeforeFinalSalesTaxLineWrites
(InvoiceNumber_TIHLNUMINV numeric(10,0) not null,
TotalTargetLineCount int,
SumTaxLineCount int,
SumNonTaxLineCount int,
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,
InvoiceNumber_TIHLNUMINV,
case countIsDifferent
when (TotalRestoreSourceLineCount - sum(SumNonTaxLineCount + SumTaxLineCount)) > 0 then '1'
else
'0'
end as countIsDifferent
from WebUIUpgrade.AuditInvoiceLineCountsBeforeFinalSalesTaxLineWrites
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.