How to Create a unique column In-DB

[davidlhowes]

In order to join two tables I need to create a unique column which will be common.

The columns which I would like to combine are different formats ("Client_No" = V_WSTRING and "Entry_No" = Int32)

I tried using the Formula in-DB tool to create a new column: Unique = "Client_No"+"Entry_No", this normally works to create a concatenation of the two fields.

I receive an error that it cannot convert the nvarchar value to data type int. I then tried to do the combination in stages to create first of all a column of type V_WSTRING that was a text version "Entry_No", thinking that I could then combine this new field to "Client_No".

This was unsuccessful as the new column remained as an INT32 despite specifying the type to be V_WSTRING, I can only presume it inherited the type from the source information.

Thanks for any input.

David

[MarqueeCrew]

Depending on your database, you should GOOGLE and find the appropriate CAST or To_String type of function that works in your SQL.  Then you can implement that expression.

Cheers,

Mark

[Thableaus]

Hi @davidlhowes 

Provide the database you're using and we'll be glad to find an appropriate function.

As @MarqueeCrew said, In-DB Formula Tool is dependent on the database syntax. 

Cheers,

[davidlhowes]

Hi Thableaus,

It is a Microsoft SQL Server database.

Thanks,

David

[MarqueeCrew · Accepted solution]

CONVERT(varchar(10), Entry_No)

[Thableaus · Accepted solution]

@davidlhowes 

Adding up to @MarqueeCrew's answer to your topic:

CONCAT (Client_No, CONVERT(varchar(10), Entry_No))

This should work for creating your Unique Column in SQL Server.

Cheers,

[davidlhowes]

Thank you all for your help