Concat two columns with space between

kzane

Hi,

I'm trying to concatenate two columns with space in between. No matter what I do I get this error:

[nQSError: 22020] Function Concat does not support non-text types.

However, if I concatenate these two columns without space it works!

I've tried a lot of options.

So, for a concrete example:

concat(CountryName, CountryId) - works, gives result: England1000

CountryName||CountryId - works, gives result: England1000

concat(concat(CountryName,' '), CountryId) - nQSError: 22020

concat(concat(cast(CountryName as varchar),' '), cast(CountryId as varchar)) - nQSError: 22020

CountryName||' '||CountryId - nQSError: 22020

cast(CountryName as varchar)||' '||cast(CountryId as varchar) - nQSError: 22020

Even if I try to join just one column (CountryName) with a custom text it does not work, for example:

CountryName||' ' - nQSError: 22020

CountryName||'-' - nQSError: 22020

concat(CountryName,' ') - nQSError: 22020

concat(CountryName,'-') - nQSError: 22020

concat(CountryName,'TEST') - nQSError: 22020

I even tried with IFNULL if nulls were causing a problem but that still didn't help.

concat(IFNULL(CountryName,''),'-') - nQSError: 22020

I tried casting as char too.

How do I solve this? It seems I've run out of options to try...

Thanks in advance

#Mayur

Edit the Formula like,

"COLUMN1" ||' '|| "COLUMN2"

kzane

Same error when I do that.

#Mayur

Same formula is working fine with me. Can you share the snap for the Formula?

kzane

Hi @Mayur_A

This is the exact formula I used:

"Countries"."CountryName" ||' '|| "Countries"."CountryCode"

or just

"Countries"."CountryName" ||'TEST'

Both give the same error that I've mentioned.

#Mayur

For the test purpose I have concatenated the simple string with the Column,

Formula I am Using,

Result,

In the same way you can use the Columns to concatenate instead of Test String.

kzane

@Mayur_A

I tried:

"Countries"."CountryName" ||' '||'Test'

Unfortunately the error I get is the same. So the issue isn't in the formula synthax, I guess. It is just weird because I can concatenate two different columns without problem if I don't use any custom text (such as 'Test').

UPDATE:

I tried joining the other column with string 'Test' and it works:

"Countries"."CountryId" ||'Test'

So it seems there's a problem with column CountryName. It is saying that it's a non-text type column, which is weird because the column has names of countries in it... I even tried the column with IFNULL and CAST to char/varchar (to get rid of nulls and convert to text-type) but nothing helps, I keep getting the same error.

cesar.advincula.o

Hello,

Maybe this error its because your column has a another Type of Data which is not CHAR OR VARCHAR, try to use CAST( AS CHAR() ) and use the connacted formula which the community give you ("Countries"."CountryName" ||' '|| "Countries"."CountryCode")

Maybe your COUNTRY CODE is on a DOUBLE Type of Data.

Try this and let me know your results.

Kind Regards,

César

kzane

Hi cesar,

both columns are type VARCHAR, both in RPD and in Database. I even tried casting to char/varchar but it doesn't work, I keep getting the same error. I would like to reiterate that this only happens for one of the two columns: CountryName and not for CountryId.

CountryId I can concatenate without problems with a custom string, eg:

"Countries"."CountryId" ||'Test' (this works!)

"Countries"."CountryName" ||'Test' (this gives nQSError: 22020)

#Mayur

Hi,

I have concatenated the VARCHAR columns with DOUBLE column in the formula it is throwing the same error as like NQSError : 22020

but, when I have cast it ,

It is working fine,

And it is also displaying the concatenated result,

Cast it again and try it.

kzane

Thanks for all the help. I cannot explain why this was an issue for me, it just didn't work as a column formula in my report. I tried casting as char/varchar several times... nothing worked. I really think this was a bug.

I finally solved it by building a new column in RPD with the same formula (CountryName || ' ' || CountryId) and it worked. No idea why the same thing wouldn't work when done in answers.