SQL & PL/SQL

Hi all

last week, we were trying to put together a series of strings, all 30 characters long, without any leading/trailing spaces, into a single string. As many of the individual strings might be all blanks, we came up with the following:

SELECT
CASE WHEN TRIM(myString1) IS NOT NULL THEN TRIM(myString1) END
||
CASE WHEN TRIM(myString2) IS NOT NULL THEN '; ' || TRIM(myString2) END
||
[..]
FROM DUAL;

When I tested this with a sample where myString1 was completely blank (30 x <space>), so that trim(myString1) would be NULL, the following error did come up:

ORA-00938: not enough arguments for function.

Since TRIM() was the only obvious function, I looked that up but didn't found anything offending. It took me some time to figure out that the problem was not TRIM, but CASE ...

SELECT WHEN condition1 THEN NULL END;

already throws the same error.

I didn't find anything about this in the documentation: http://docs.oracle.com/cd/E11882_01/server.112/e26088/expressions004.htm reads:

<quote>
In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.
</quote>

[We found an acceptable workaround here:

CASE WHEN TRIM(myString1) IS NOT NULL THEN TRIM(myString1) || ' ' END

But we're still baffled. Any thoughts/ideas welcome!

Greetinx from Switzerland
NextName