Case expressions may only be nested to level 10

dataservices job design
1

I have more than 10 lines in decode and ifthenelse function, I have this error when run the job.

<[Microsoft][ODBC SQL Server Driver][SQL Server]Case expressions may only be nested to level 10.>

If I only put 10 lines, it runs ok.

Any alternative way guys? I don’t want to create a stored procedure to update the data.

Please help, thanks.

ylai20 :malaysia: (BOB member since 2008-05-21)

2

Technically: use a decode().

But functionally: is there another way? Nesting more than 10 times seems a bit excessive.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

3

If I use decode, it also given me the same error. I suspect it cannot more than 10 level.

The another way I can think of is to create a store procedure using case when statement to update…is this the only way?

Pls advise…

ylai20 :malaysia: (BOB member since 2008-05-21)

4

So when you use DECODE, does it gets pushed down?

ganeshxp :us: (BOB member since 2008-07-17)

5

Ganesh, It looks like the error is coming from SQL Server so I would have to assume that it is pushed down.

Ylai20,
What version of SQL Server are you using? It sounds like a very old version. My install of SQL Server 2008R2 allows more than 10 case expressions.

Post some code so we can see what you’re doing.

eganjp :us: (BOB member since 2007-09-12)

6

My immediate thought was that it’s a SQL Server limitation rather than DS. I think the problem may be that the generated SQL is nested CASEs rather than a single linear CASE statement:

so you get

CASE …
WHEN …
ELSE
…CASE …
…WHEN …
…ELSE
…CASE …
…WHEN …
…ELSE …
…END
…END
END

instead of
CASE …
WHEN …
WHEN …
ELSE
END

(excuse the full stops, the BB takes out spaces, and my T_SQL syntax may not be quite right, but it should be enough to demonstrate my point)

This is because decode() allows for conditions of different types to be evaluated in a single statement, and DS should push those down as a set of nested SQL if - then statements. A SQL CASE should be used to evaluate different conditions against a single variable, but that isn’t how the decode() syntax works.

I seem to recall Oracle has a limit on how many WHEN clauses you can have in a single CASE - maybe 128? - I know I hit this limit once or twice.

dastocks (BOB member since 2006-12-11)

7

Yes, the code is like
case when then
else (case when…

So i suspect SQL 2008 cannot handle it…

I am using 2008R2, not sure about 2012…

ylai20 :malaysia: (BOB member since 2008-05-21)

8

Hi ylai20

I also faced similar issue when using decode in BODS 4.0 where in we were not able to execute the decode() with more than 10 conditions.

Its a licensing issue where in SQL Server 2008 R2 has a limitation to process 10 conditions in CASE .

Arrange the 10 statements using decode() and remaining conditions in another decode statement. Combine both the decode statements using an OR statement.

This should work.

Either you can create a function in SQL Server which acts like decode

Regards
Arun Sasi

searchforarun :india: (BOB member since 2012-10-09)