SAP Knowledge Base Article - Preview

2783982 - An incorrect result may be returned from an outer join query including a CASE statement - SAP ASE

Symptom

  • A join query with OUTER JOIN clauses on a condition including a CASE statement may return an incorrect result set.
  • The CASE statement on the OUTER JOIN condition possibly returns a NULL value.
  • This problem may occur if merge joins or hash joins are chosen, and it does not occur if only nested loop joins are used.
  • Below is a sample query which possibly causes this problem :

SELECT test_a.a_col1, test_a.a_col2, test_b.b_col4, test_c.c_col4
  FROM test_a
  LEFT OUTER JOIN test_b ON test_a.a_col1 = test_b.b_col1 and test_a.a_col2 = test_b.b_col2 and test_b.b_col3 = 'T001'
  LEFT OUTER JOIN test_c ON test_c.c_col1 =  CASE
                                                                       WHEN test_a.a_col2 = 'TEST01' THEN '1'
                                                                       ELSE null
                                                                  END

  • Even if the ELSE clause is not specified, the default is ELSE NULL. So, the same problem can occur.

Read more...

Environment

  • SAP Adaptive Server Enterprise 15.7
  • SAP Adaptive Server Enterprise 16.0

Product

SAP Adaptive Server Enterprise 15.7 ; SAP Adaptive Server Enterprise 16.0

Keywords

CR819088, CR#819088, CR 819088, incorrect result, wrong result, case, NULL, merge join, hash join , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.