Oracle Transactional Business Intelligence

Products Banner

Concatenate non text fields

Received Response
52
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.

Answers

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

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

  • Thank you both so much for your quick response!

  • 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