Oracle Transactional Business Intelligence

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

Concatenate non text fields

Received Response
155
Views
4
Comments

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Answers

  • 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

  • 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))

  • Rank 1 - Community Starter

    Thank you both so much for your quick response!

  • 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

Welcome!

It looks like you're new here. Sign in or register to get started.