1 Reply Latest reply on Jan 3, 2019 9:35 AM by Steven Hawkins

    Teiid is returning null for query which has concatenation expression if one of the field used in concatenation operation is null

    Rujuta Samant Newbie

      Hi team ,

      We observed Teiid is returning null result for query which has concatenation expression if one of the field used in concatenation operation is null.

      I am using teiid-9.1.3 version.

       

      My Query: It should provide AddressLine1 , AddressLine2 ,Address Line 3 and concatenation of all  (AddressLine1+ AddressLine2+Address Line 3)

       

      SELECT ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_1,ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_2, ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_3,CONCAT( ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_1,CONCAT(' ', CONCAT( ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_2,CONCAT(' ', ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_3)))) FROM   ETL_ORACLE.DQORA.et_patient   INNER JOIN ETL_ORACLE.DQORA.et_visit ON ETL_ORACLE.DQORA.et_patient.PATIENT_ID = ETL_ORACLE.DQORA.et_visit.PATIENT_ID   INNER JOIN ETL_ORACLE.DQORA.et_hospital ON ETL_ORACLE.DQORA.et_visit.HOSPITAL_ID = ETL_ORACLE.DQORA.et_hospital.HOSPITAL_ID   INNER JOIN ETL_ORACLE.DQORA.et_doctor ON ETL_ORACLE.DQORA.et_visit.DOCTOR_ID = ETL_ORACLE.DQORA.et_doctor.DOCTOR_ID WHERE   ETL_ORACLE.DQORA.et_patient.id <= '10000' AND ETL_ORACLE.DQORA.et_visit.id <= '10000' limit 1500;

       

       

      Result:

      ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3, expr,

      1: VALLEY, WEST, CENTER ,VALLEY WEST CENTER

      2: WASHINGTON ,TOWN ,HALL ,WASHINGTON TOWN HALL

      3: RUBIX, CENTER, null, null

      4: ROCKFELLER,GARDEN,null,null

      5: KEVIN,HEIGHT,null,null

       

       

      Regards,

      Rujuta S