mercredi 11 novembre 2015

T-SQL Replacing If Else (Case When) with lookup table

I'm having troubles replacing a bloated If Else structure. Here is an example:

If ColA startswith 'ABC' then 'Ext' else
If ColB startswith 'DBC' then 'Int' else
If ColC startswith ('CCC', 'DDD','EEE', ..) then 'N/A'

.... and so on.

I know I can rewrite this into a Case When structure. The problem is that this needs to be stored in a table where the max length is nvarchar(4000). I tried to solve this with a lookup table looking like this:

lookupColumnName | lookupValue | lookupResultName | lookupResult

I was trying to write my query in a more "generic" way like:

SELECT 
   (SELECT lookupResult from lookupTable where lookupColumn = 'ColA')
FROM
SourceTable

Here is my first try to implement the order of the origanel If Else structure:

CASE WHEN
    CASE WHEN
        CASE WHEN 
        (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'BKTXT' AND BKPF_BKTXT like lookupValue order by len(lookupValue) DESC 
        ) IS NULL THEN (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'XBLNR' AND BKPF_XBLNR like lookupValue order by len(lookupValue) DESC
        ) ELSE (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'BKTXT' AND BKPF_BKTXT like lookupValue order by len(lookupValue) DESC 
        ) END 
        <> 'LEER' THEN
        CASE WHEN 
        (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'BKTXT' AND BKPF_BKTXT like lookupValue order by len(lookupValue) DESC 
        ) IS NULL THEN (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'XBLNR' AND BKPF_XBLNR like lookupValue order by len(lookupValue) DESC
        ) ELSE (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'BKTXT' AND BKPF_BKTXT like lookupValue order by len(lookupValue) DESC 
        ) END
        ELSE
        (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'SGTXT' AND COEP_SGTXT like lookupValue order by len(lookupValue) DESC
        )
    END IS NULL THEN 'App'
    ELSE
    CASE WHEN
        CASE WHEN 
        (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'BKTXT' AND BKPF_BKTXT like lookupValue order by len(lookupValue) DESC 
        ) IS NULL THEN (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'XBLNR' AND BKPF_XBLNR like lookupValue order by len(lookupValue) DESC
        ) ELSE (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'BKTXT' AND BKPF_BKTXT like lookupValue order by len(lookupValue) DESC 
        ) END 
        <> 'LEER' THEN
        CASE WHEN 
        (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'BKTXT' AND BKPF_BKTXT like lookupValue order by len(lookupValue) DESC 
        ) IS NULL THEN (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'XBLNR' AND BKPF_XBLNR like lookupValue order by len(lookupValue) DESC
        ) ELSE (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'BKTXT' AND BKPF_BKTXT like lookupValue order by len(lookupValue) DESC 
        ) END
        ELSE
        (
        SELECT TOP 1 lookupResult FROM ##lookupDefinition where lookupColumnName = 'SGTXT' AND COEP_SGTXT like lookupValue order by len(lookupValue) DESC
        )
    END
END

But this approach could also result in a string containing more than 4000 characters.

Right now I'm stuck. Is there a better way?

Aucun commentaire:

Enregistrer un commentaire