Oracle Transactional Business Intelligence Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› More Solutions› Oracle Transactional Business Intelligence Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Concatenate non text fields

Received Response
192
Views
4
Comments
Lin Tiedemann
Lin Tiedemann Rank 1 - Community Starter
in Oracle Transactional Business Intelligence Forum

Summary

Function Concat does not support non-text types.

Content

Is there a way to concatenate non-text fields? I would like to concatenate Req id and Candidate id to create a unique field.

"Requisition Identification"."Req. Identifier"||'-'||"Candidate Identification"."Candidate Identifier" 

 Function Concat does not support non-text types.

Tagged:

Answers

  • Jagadish Angadi-29558
    Jagadish Angadi-29558 Rank 3 - Community Apprentice

    Hi,

    Please use this code. It will work, all you have to do is convert both Identifiers as char.

    Cast ("Requisition Identification"."Req. Identifier" as char)||'-'|| cast ("Candidate Identification"."Candidate Identifier" as char)

     

    Thanks

    Jagadish

  • Prudence K
    Prudence K Rank 5 - Community Champion

    Hi Lin,

    You need to cast the non-text type to text type.  Try this:

    CAST("Requisition Identification"."Req. Identifier" AS VARCHAR(10))||'-'||CAST("Candidate Identification"."Candidate Identifier" AS VARCHAR(10))

  • Lin Tiedemann
    Lin Tiedemann Rank 1 - Community Starter

    Thank you both so much for your quick response!

  • Reena Trangri
    Reena Trangri Rank 3 - Community Apprentice

    Hi ,

    The above statements would work, but just wanted to mention that CONCAT function will work as well when both are cast to character datatypes, and in the above case, nested CONCAT may be needed, as Req no and Cand id needs to be separated by a "-"

    CONCAT(CONCAT(CAST("Requisition Identification"."Req. Identifier" AS VARCHAR(10)), '-'), CAST("Candidate Identification"."Candidate Identifier" AS VARCHAR(10))) 

    Hope this helps.

    Thanks and Regards,

    Reena Trangri