[ http://issues.apache.org/jira/browse/DERBY-1620?page=comments#action_12424969 ] Andrew McIntyre commented on DERBY-1620: ----------------------------------------
Removed SQL2003_Standard_Case_Expression.doc attachment at attcher's request. > 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, 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