Convert a column from varchar to number in expression building window

[Aami]

Hi All,

I am trying to build a lookup from table A to Table B. I would like to convert one of the columns from varchar to number in expression builder where the condition is being defined for comparision purpose. Any  kind of help is appreciated.

Thanks,

[TimGarrod · Accepted solution]

Hi Aami - assuming this is a lookup in Compose for Data Warehouses - you will use whatever data conversion function(s) / syntax is used in your data warehouse environment.   ComposeDW uses an E-LT paradigm to process data.  Meaning it generates SQL for your target DW platform to process the data.   Thus any expressions / transformation logic applied in ComposeDW uses your target platforms SQL functionality. 

For example if SQL Server is your data warehouse platform - SQL Server provides CAST(X as [DATATYPE]) ,  CONVERT(DATATYPE,X) or TRY_CONVERT(DATATYPE,X)  as data type casting operators.  

If you are using Snowflake -  CAST(X as DATATYPE) or the TRY_CAST(X as DATATYPE) functions support data type conversion. 

Each DW environment provides its own functionality.  It is a good practice to use "TRY_" functions in the event your VARCHAR data cannot be convert to numeric - it will prevent an error and you could even use Compose data validation functionality to route those records to an error mart if required. 

Hope this helps.

[Aami]

Thank you Tim. I was able to use TRY_ in expression builder and it worked for me.