sql - Create Table Computed Value

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')

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')

Share Improve this question edited Feb 7 at 8:00 asked Feb 6 at 18:59 gchq 1,7712 gold badges30 silver badges54 bronze badges

2 Have you looked at the IF...ELSE syntax in the docs ? Hint: use a CASE statement. Also, why are there double quotes around those column names? – devlin carnate Commented Feb 6 at 19:52
5 Furthermore, in SQL Server, I would strongly recommend against using float for things like money and tax amounts - these values are not precise and subject to rounding errors. Use decimal(p,s) instead – marc_s Commented Feb 6 at 20:10
This question is similar to: T-SQL IF statement embedded in a sum() function. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – Thom A Commented Feb 6 at 21:46
FYI case is an expression (not a statement) which is what means it is able to be used where an if cannot. – Dale K Commented Feb 6 at 23:37
I have updated the question to answer some of the questions. It's a conversion tool that I am working on so things like double quotes (now resolved) are being dragged in from SQL Anywhere - I seem to remember seeing somewhere that with MS SQL you need to turn off identity before importing data and then turn it back on again, but at the moment I can't find that reference again. – gchq Commented Feb 7 at 8:05

Add a comment |

1 Answer 1

Sorted by: Reset to default 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) ,
    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 SYSDATETIME()
    CONSTRAINT PK_A_Invoices_Invoice_ID PRIMARY KEY (Invoice_ID)

);

Fiddle

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

sql - Create Table Computed Value - Stack Overflow

1 Answer 1

与本文相关的文章

评论列表(0)