SQL syntax in SQL Server 2019

[nector]

Can someone help me correct what is wrong with these letters in the SQL syntax below:

(1) "D" & "C3" line 16

SELECT DISTINCTROW tblProducts.ProductID, tblProducts.ProductName, tblProducts.BarCode, tblSalesTax.TaxClass, tblPricing.Prices, tblPricing.RRP, tblSalesTax.VatRate, tblSalesTax.Tourism, tblSalesTax.Insurance, tblSalesTax.TourismLevy, tblPricing.TaxInclusive, tblSalesTax.InsuranceRate, tblSalesTax.InsuranceRate AS Premium, IIf( ([TaxClass] = "D"), Round( IIf(([TaxClass] = "D"), [Prices],([Prices] / 1.16)), 2 ), Round( IIf(([TaxClass] = "C3"), [Prices],([Prices] / 1.16)), 2 ) ) AS ExportPrice, tblPricing.NoTaxes, tblProducts.Sales FROM tblSalesTax INNER JOIN ( tblProducts INNER JOIN ( tblcartergory INNER JOIN tblPricing ON tblcartergory.CartID = tblPricing.CartID ) ON tblProducts.ProductID = tblPricing.ProductID ) ON tblSalesTax.IDClass = tblPricing.IDClass WHERE (((tblProducts.Sales) = Yes)) ORDER BY tblProducts.ProductID DESC;

 

[theDBguy]

Have you tried using single quotes in place of double quotes?

 

[GPGeorge]

Yup, theDBGuy is right about the proper string delimiter for T-SQL as opposed to Access SQL.

 

[plog]

Logically:

IIf(
   ([TaxClass] = "D"),
   Round(
     IIf(([TaxClass] = "D"), [Prices],([Prices] / 1.16)),
     2
   ),
   Round(
     IIf(([TaxClass] = "C3"), [Prices],([Prices] / 1.16)),
     2
   )
) AS ExportPrice,

That only needs to be one IF instead of 3.

 

[Pat Hartman]

I don't see any difference in the two calculations. AND there is no Else option. AND the Round() can go outside the primary IIf()

 

[sonic8]

Can someone help me correct what is wrong with these letters in the SQL syntax below:

(1) "D" & "C3" line 16

As others pointed out correctly, the primary delimiter for string literals in T-SQL is a single quote.
You can also enable double quotes as delimiter by setting SET QUOTED_IDENTIFIER OFF. This can also be configured in the ODBC connection settings. However, I would only see this as a temporary solution during an migration or for legacy applications. Some SQL Server functionality requires QUOTED_IDENTIFIER=ON. So, I would try to only use single quotes for any future development work.

 

[nector]

Many thanks for the comments

 

[Pat Hartman]

[Prices],([Prices] / 1.16)

Clearly, I'm missing something.

How is this:

IIf(
   ([TaxClass] = "D"),
   Round(
     IIf(([TaxClass] = "D"), [Prices],([Prices] / 1.16)),
     2
   ),
   Round(
     IIf(([TaxClass] = "C3"), [Prices],([Prices] / 1.16)),
     2
   )
) AS ExportPrice,

Different from This:

 Round(IIf(TaxClass = "D" OR TaxClass = "C3", 
      Prices, (Prices / 1.16)),2) AS ExportPrice,

 

[cheekybuddha]

Round(IIf(TaxClass = "D" OR TaxClass = "C3", Prices, (Prices / 1.16)),2) AS ExportPrice,

Or different from:

Round(
  Prices / IIF(TaxClass IN ('D', 'C3'), 1, 1.16),
  2
) AS ExportPrice

 

[Galaxiom]

Using a hard coded formula isn't a great idea.

A better plan would be to store the factor in a table related to TaxClass. Any future changes to the values or even a new TaxClass can be simply updated in the table.

 

[Pat Hartman]

Not sure In() works there. Did you test?

 

[cheekybuddha]

Not sure In() works there. Did you test?

Yes, works for me (in T-SQL)

 

[cheekybuddha]

SELECT TOP 20
  i.InvoiceID,
  i.RegNo,
  i.Amount,
  ROUND(
    i.Amount * IIF(i.RegNo IN ('772 LTM', 'LC10 CPK'), 1, 2),
    2
  ) AS ExportPrice
FROM Invoices i
;

InvoiceID|RegNo   |Amount |ExportPrice|
---------+--------+-------+-----------+
    10001|LC10 CPK| 126.56|     126.56|
    10002|LC10 CHX| 461.56|     923.12|
    10003|LF12 ZRG| 341.38|     682.76|
    10004|772 LTM |1253.55|    1253.55|
    10005|772 LTM |5179.08|    5179.08|
    10006|LJ58 NTM| 238.80|     477.60|
    10007|LC10 CPK| 387.28|     387.28|
    10008|LC10 CHX| 645.26|    1290.52|
    10009|LC10 CPK| 387.28|     387.28|
    10010|LC10 CHY| 401.60|     803.20|
    10011|LC10 CPK| 126.56|     126.56|
    10012|LJ58 NTM| 313.80|     627.60|
    10013|LC10 CHY| 697.47|    1394.94|
    10014|LM10 MEV|3685.55|    7371.10|
    10015|        |1265.62|    2531.24|
    10016|RO12 UCJ| 312.41|     624.82|
    10017|LC10 CPK| 216.00|     216.00|
    10018|LC10 CPK| 246.00|     246.00|
    10019|LC10 CPK| 222.50|     222.50|
    10020|LF12 ZRC|2456.20|    4912.40|