3 Replies Latest reply on Dec 3, 2018 8:06 AM by Mark Tawk

    Case when with aggregated subquery

    Mark Tawk Novice

      Hello,

      I migrated lately from Teiid 9 to Teiid 10.2.1.

      I have the below simplified query using oracle translator that works with Teiid 9 but not with Teiid 10.2.1

      The issue happens when having a case when expression using a string field from main query and another from an aggregated subquery.

      If aggregation is removed from subquery, it executes successfully.

       

      select CASE WHEN  ( "Product_Sub"."product_sub" IS null )

              THEN "Product"."Product"

              ELSE "Product_Sub"."product_sub"  END  as "CaseField1"

      from "orcldbdataositModel_WZ"."OrclSchema"."Product" "Product"

      ,(select  "Product_sub"."Product"  as "product_sub", count(1)

      from "orcldbdataositModel_WZ"."OrclSchema"."Product" "Product_sub"

      group by "Product_sub"."Product") "Product_Sub" 

       

      For some reason, it is considering that "Product"."Product" and "Product_Sub"."product_sub"  are from different data types even though they are coming from the same table.

      So the case is written in jdbc like that: CASE WHEN v_0.c_0 IS NULL THEN TO_NCHAR(g_0."Product") ELSE v_0.c_0 END

      the use of TO_NCHAR is resulting the oracle error: SQLException: ORA-12704: character set mismatch

      CASE WHEN v_0.c_0 IS NULL THEN TO_NCHAR(g_0."Product") ELSE v_0.c_0 END