SAP Knowledge Base Article - Preview

2266027 - IQ16 Incorrect result at Outer Join and CASE expr in derived table

Symptom

Customer found a simple query returned an incorrect result during IQ upgrade process.
This query uses ansi outer join with a derived table which contains a CASE expression.

       aa,BBaa,case_aa
       ---------------------
       0,(NULL),(NULL)  ==> expected correct result
   
       aa,BBaa,case_aa
       ---------------------
       0,(NULL),'B'     ==> incorrect result for output case_aa ('B')

Customer wants this problem to be fixed as soon as possible.

[repro]

   Create table kb001 ( aa int );
   Insert into kb001 values(0) ;
   Commit ;

   select  AA.aa, CC.BBaa, CC.case_aa
     from  kb001 AA  left outer join
         (select  cast(BB.aa + 1 as int) as BBaa
                 ,case when BB.aa = 0 then 'A' else 'B' end case_aa
            from  kb001 BB
         ) CC
       on AA.aa = CC.BBaa
   ;

   --returned incorrect result
  
     aa,BBaa,case_aa
     ---------------------
     0,(NULL),'B'

   The output for case_aa('B') is incorrect. It should be returned as 'NULL'.
   In order to get the correct result, the query should replace the derived table to
   a temporary table.


Read more...

Environment

SAP IQ 16.0

Product

SAP IQ 16.0 ; SAP Sybase IQ 15.2 ; SAP Sybase IQ 15.4 ; Sybase IQ 12.7

Keywords

Incorrect result, outer jon, CR 794494, CR794494 794494, , KBA , BC-SYB-IQ , Sybase IQ , Problem

About this page

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

Search for additional results

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