CODEWITHSUNDEEP
sqlsql-server
user3734454
user3734454

Reputation: 59

Changing query from access SQL to SQL server

I created this query in Microsoft access and I am trying to run it in SQL Server 2012 but I am getting several errors I have tried a few different ways to fix it but I still cant get it working

SELECT Choose(Iif(IsNull([TaxCodeChangeTypeID]),0,
                   [TaxCodeChangeTypeID])+1,
                   'UNKNOWN',
                   'UPLIFT',
                   'PAPERP6',
                   'PAPERP9',
                   'P453',
                   'P46',
                   'HMRC',
                   'DPSP6',
                   'DPSP9',
                   'P46(Pen)',
                   'P46(Expat)') AS [Tax Code Change Type]
FROM EeBals

Upvotes: 1

Views: 59

Answers (2)

DrCopyPaste
DrCopyPaste

Reputation: 4117

Not being able to test it, but as it seems ISNULL should be the only issue here.

In MS Access, ISNULL returns true if the checked value is in fact null, but in SQL Server it works like a COALESCE (but only taking 2 instead of unlimited parameters) returning the second value if the first was null.

CHOOSE and Iif should work similarily to MS Access in SQL Server. I would simply replace IsNull([TaxCodeChangeTypeID]) with [TaxCodeChangeTypeID] IS NULL

SELECT Choose
    (
        Iif
        (
            [TaxCodeChangeTypeID] IS NULL,
            0,
            [TaxCodeChangeTypeID]
        )+1,

        'UNKNOWN','UPLIFT','PAPERP6','PAPERP9','P453','P46','HMRC','DPSP6','DPSP9','P46(Pen)','P46(Expat)'
    ) AS [Tax Code Change Type]
FROM EeBals

Note: this only applies to SQL Server since the version 2012, before that neither IIF nor CHOOSE were present in either standard SQL Server or SQL Server Express

PS: of course the whole IIf(...)+1 can now be avoided and be a little more beautified as GarethD also mentioned in his post by writing ISNULL([TaxCodeChangeTypeID], 0) + 1 instead.

Upvotes: 2

GarethD
GarethD

Reputation: 69759

As mentioned in comments, ISNULL in Access takes a single parameter and returns a boolean value depending on whether the parameter passed is null, in SQL Server ISNULL takes two arguments, the first is the value to check, and the second is the value to return if the value is null, so your line:

Iif(IsNull([TaxCodeChangeTypeID]),0, [TaxCodeChangeTypeID])+1

Needs to be replaced with 

ISNULL([TaxCodeChangeTypeID], 0) + 1

To acheive the same thing. Making your full query:

SELECT  CHOOSE(ISNULL([TaxCodeChangeTypeID], 0) + 1,
                   'UNKNOWN',
                   'UPLIFT',
                   'PAPERP6',
                   'PAPERP9',
                   'P453',
                   'P46',
                   'HMRC',
                   'DPSP6',
                   'DPSP9',
                   'P46(Pen)',
                   'P46(Expat)') AS [Tax Code Change Type]
FROM EeBals

Upvotes: 3

Related Questions