Create Table Computed Value

Question

Sadly, SAP are abandoning SQL and I am currently writing a utility to move to SQL Server. One of the tables being created has a computed column (taken directly from the exported data) and SQL Server has thrown an error

Incorrect syntax near the keyword 'if'

The generated table after changing the types is...

CREATE TABLE A_Invoices
(
    Invoice_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    Invoice_No int, 
    Type varchar(2), 
    Invoice_Date date, 
    Transaction_Date date DEFAULT GETDATE(), 
    Customer_ID int, 
    Customer_Name varchar(100), 
    Nominal_ID int, 
    Add1 varchar(100), 
    Add2 varchar(200), 
    City varchar(100), 
    State varchar(2), 
    Zip varchar(15), 
    Terms varchar(100), 
    Amount_Net float, 
    Amount_Tax float, 
    Amount_Gross float, 
    Tax_Rate float(53), 
    Tax_Code varchar(2), 
    Settlement_Due_Days int, 
    Settlement_Discount_Rate float(53) DEFAULT 0.0, 
    Settlement_Discount_Amount AS 
        IF "Settlement_Discount_Rate" > 0 
           THEN ("Amount_Net"*"Settlement_Discount_Rate") / 100 
           ELSE 0.0 
        ENDIF, 
    Printed varchar(1) DEFAULT 'N', 
    Posted varchar(1) DEFAULT 'N', 
    Flag_Prepayment varchar(1) DEFAULT 'N', 
    Prepayment_Months int DEFAULT 0, 
    Prepayment_Amount float DEFAULT 0.0, 
    Flag_Deposit varchar(1) DEFAULT 'N', 
    Flag_Ad_Hoc varchar(1) DEFAULT 'N', 
    Customer_Order_No varchar(50), 
    Details varchar(100), 
    Pay_Due_Days int DEFAULT 0, 
    Open_Editing int DEFAULT 0, 
    Editing_Name varchar(100) DEFAULT 'System', 
    Updated_Name varchar(100) DEFAULT 'System', 
    Updated DateTime2 DEFAULT GETDATE()
);

Any ideas on the syntax? Not really sure how to deal with this.

**UPDATE

To answer some of the questions - the double quotes came back with the SQL Anywhere query.

SELECT c.column_name AS 'Name', 
c.base_type_str AS 'Type',
c.nulls AS 'Nulls', 
c.`default` AS 'Default' 
FROM systabcol c KEY JOIN systab t ON t.table_name = 'vTB' 

Most of these tables have been upgraded from early versions of SQL Anywhere.

Thank you for your input, it's now working - I got round the 'if' with this...

If vComputed = "C" Then
vMSComputed = "Y"
If vDefault.Contains("if") Then
    Dim s As New System.Text.StringBuilder(vDefault)
    s.Replace("if", "CASE WHEN", 0, 2)
    s.Replace("endif", "END", 0, s.Length)
    s.Replace("""", "")
    vMSDefault = s.ToString

End If
Else
vMSComputed = "N"
End If

For some strange reason it created the MS SQL table then returned an error saying it already existed. Got round that with...

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'vTable')

Answer 1

Like mentioned in the comments, here are a few corrections

• CASE expression instead of IF/ELSE

• Decimal instead of float for precision(this is optional but this is a better practice in this case ). You can adjust p,s as required.

• Quotes removed, not sure why you used them.

    CREATE TABLE A_Invoices
    (
        Invoice_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
        Invoice_No int, 
        Type varchar(2), 
        Invoice_Date date, 
        Transaction_Date date DEFAULT GETDATE(), 
        Customer_ID int, 
        Customer_Name varchar(100), 
        Nominal_ID int, 
        Add1 varchar(100), 
        Add2 varchar(200), 
        City varchar(100), 
        State varchar(2), 
        Zip varchar(15), 
        Terms varchar(100), 
        Amount_Net decimal(18, 2), 
        Amount_Tax decimal(18, 2), 
        Amount_Gross decimal(18, 2), 
        Tax_Rate decimal(18, 2), 
        Tax_Code varchar(2), 
        Settlement_Due_Days int, 
        Settlement_Discount_Rate decimal(18, 2) DEFAULT 0.0, 
        Settlement_Discount_Amount AS 
            CASE 
                WHEN Settlement_Discount_Rate > 0 
                THEN (Amount_Net * Settlement_Discount_Rate) / 100 
                ELSE 0.0 
            END, 
        Printed varchar(1) DEFAULT 'N', 
        Posted varchar(1) DEFAULT 'N', 
        Flag_Prepayment varchar(1) DEFAULT 'N', 
        Prepayment_Months int DEFAULT 0, 
        Prepayment_Amount decimal(18, 2) DEFAULT 0.0, 
        Flag_Deposit varchar(1) DEFAULT 'N', 
        Flag_Ad_Hoc varchar(1) DEFAULT 'N', 
        Customer_Order_No varchar(50), 
        Details varchar(100), 
        Pay_Due_Days int DEFAULT 0, 
        Open_Editing int DEFAULT 0, 
        Editing_Name varchar(100) DEFAULT 'System', 
        Updated_Name varchar(100) DEFAULT 'System', 
        Updated DateTime2 DEFAULT GETDATE()
    );

Fiddle