Installation WinXP SP2, OO.o 1.9.123
I am experiencing rather odd behavior between queries and viees when
using case when and coalese commands.
Here are the steps to reproduce the effect.
Created HSQL embedded datasource using build 1.9.123
Execute the following commands in the sql window. These are taken from
the HSQL test cases supplied with the 1.8.0 distro.
create table testCase(id integer, data varchar(10), filler varchar(10));
insert into testCase values(100,'xxxx',null);
insert into testCase values(200,'yyyy',null);
insert into testCase values(300,null,null);
Now open a new query window in SQL view, add the following select statement.
SELECT CASE DATA WHEN 'xxxx' THEN 'X' WHEN 'yyyy' THEN 'Y' WHEN 'zzzz'
THEN 'Z' ELSE 'NOTFOUND' END FROM TESTCASE
Run the query. I get are three rows with NULL values.
Now turn the 'Run SQL directly' button off. Attempt to run the query
again. The following error is reported:
"syntax error unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE"
Select the 'Run SQL directly' button again. Save the query. (mine is
named CASE1)
Select 'Create as View' on new query. Mine is named vCASE1
Open the view and I get the following three rows of data:
X
Y
NOTFOUND
This is the correct results.
Execute the Query again, and three rows with NULL values.
The coalese command is giving me the same behavior.
Not sure if this is an issue for OO.o or for HSQLDB.org?
Thanks
Andrew Jensen
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
