concatenating columns in Cognos CostPoint-CodePudding

this is probably simple but my brain is fried...

trying to add a column in cognos analytics to combine first name and last name while using preferred name in that case where a preferred name is present.

i used the case function below to return the correct first name (preferred name if present, first name if no preferred name), but looking to add in the last name to return "full name" ("First_Name/Prefered_Name" "Last_Name")

Case
  when [prefered name] = ' ' 
    then [first name] 
  else [prefered name]
end

CodePudding user response：

It may depend on which RDBMS you are using.

SQL Server includes the CONCAT() function. I tried this and Cognos allows only two arguments. So maybe Cognos has defined its own CONCAT function.

SQL Server also allows string concatenation using the operator.

Oracle allows string concatenation using the || operator. This is also listed as the string concatenation operator on the Functions tab in the Expression editor.

Case
  when [prefered name] = ' ' 
    then [first name] || ' ' || [last name]
  else [prefered name]
end