Data type conversion

2 followers
0
Avatar

I am trying to reconstruct a join that was build in some other system using sql to Datameer. Below is the example used to describe the problem:

 

Select x,y,z,

from table a

left join table b

on convert(numeric(30), a.col_f ) = convert(numeric(30), a.col_g )

 

I have tried converting col_f into integer (as col_g is a string of 15 character, converted both into integer to maintain consistency for join) in datameer but I am unable to get the result. Tried couple of different methods but still unable to get it, col_f is a string with 26 characters, which has 11 zeros at the beginning while col_g is a string with length of 15 (no zeros). Tried using substr to remove zeros, any idea how can I approach this problem to get my result for join.

 

Thank you 

Jayraj

2 comments

  • Avatar
    Joel Stewart

    I'd recommend using the INT function directly in Datameer to convert strings (even with leading 0's) into an integer. Once both numbers are integers, the joining should be straightforward. 

    For example INT("0000000000012345") converts the string into the integer 12345. Hope this helps! 

    0
  • Avatar
    Jayraj

    Thank you very much Joel, tried using it and yields the result. But it was crucial to maintain col_f and col_g as string. Appreciate the help, I did it using SUBSTR and removing the leading zeros.

    0
Please sign in to leave a comment.