Link to home
Start Free TrialLog in
Avatar of Paula DiTallo
Paula DiTalloFlag for United States of America

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?




ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial