[ http://issues.apache.org/jira/browse/DERBY-1620?page=comments#action_12424967 ] John Peterson commented on DERBY-1620: --------------------------------------
It has been brought to my attention that the SQL2003_Standard_Case_Expression.doc is a violation of the ISO copyright, I suggest it be removed as soon as possible. Instead, please refer to http://en.wikipedia.org/wiki/SQL:2003 for a link to a .zip file (sql_2003_standard.zip), and open the 5WD-02-Foundation-2003-09.pdf within. Turn to page 197 (221 of 1332) (Chapter 6.11) to view the Case Expression standard. > SQL CASE statement returns ERROR 42X89 when including NULL as a return value > ---------------------------------------------------------------------------- > > Key: DERBY-1620 > URL: http://issues.apache.org/jira/browse/DERBY-1620 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.1.3.1 > Environment: Windows XP > Reporter: John Peterson > Attachments: Derby_Community_Discussion.doc, > SQL2003_Standard_Case_Expression.doc, sysinfo_and_example.txt > > > This bug appears to be related to the DERBY-7 bug (NULLIF() function). When > NULL is used during a CASE statement, Derby requires the NULL to be CAST to > the appropriate type. This does not appear to meet the SQL 2003 Standard for > the Case Expression (see attached Word document). See the attached Word > document to view the Derby Community Discussion about this issue. See the > attached .TXT to view the SYSINFO and to see an example of the steps to > reproduce using IJ. > Steps to Reproduce: > ij>values case when 1=2 then 3 else NULL end; > ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible. Neither > type is assignable to the other type. > Current Workaround: > ij>values case when 1=2 then 3 else cast(NULL as INT) end; -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
