Data type conversion

Follow

Jayraj

• October 11, 2018 19:49
• Edited

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 

0

• 

  Joel Stewart

  • October 12, 2018 20:40

  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

  Comment actions Permalink
• 

  Jayraj

  • October 15, 2018 14:28
  • Edited

  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

  Comment actions Permalink

Please sign in to leave a comment.