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