Sql functions CONVERT and CAST

[sajdre]

How can I use CONVERT or CAST sql functions in cube js schema? I try the following for dimension:

attendancePercent: {
      sql: `CONVERT(INT, ${CUBE}.\`Attendance percent\`)`,
      type: `number`
}

and get error: unexpected INT at position 25 near INT.

MongoBI connector supports CONVERT and CAST functions. Do I do something wrong or it's cubejs issue?

[hassankhan]

Hi @sajdre, I think you're supposed to pass the type as the second parameter to CONVERT():

attendancePercent: {
  sql: `CONVERT(attendancePercent, INT)`,
  type: `number`,
}

[sajdre]

Hi @sajdre, I think you're supposed to pass the type as the second parameter to CONVERT():

attendancePercent: {
  sql: `CONVERT(attendancePercent, INT)`,
  type: `number`,
}

Without success:

{
....
    attendancePercent: {
      sql: `${CUBE}.\`Attendance percent\``,
      type: `string`
    },

    attendancePercentNum: {
      sql: `CONVERT(${CUBE}.\`Attendance percent\`, INT)`,
      type: `number`
    },
....
}

and

'{"error":"Error: parse sql \'SELECT\\n
    ... 
    \\`tee103e2d3cd24128a1647dc9bd349ace`.`Attendance percent` 
    `tee103e2d3cd24128a1647dc9bd349ace__attendance_percent`, 
    CONVERT(`tee103e2d3cd24128a1647dc9bd349ace`.`Attendance percent`, INT) 
    `tee103e2d3cd24128a1647dc9bd349ace__attendance_percent_num`, ....`\\n    
FROM\\n      mvesc.tee103e2d3cd24128a1647dc9bd349ace AS `tee103e2d3cd24128a1647dc9bd349ace`\\n  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 ORDER BY 1 ASC LIMIT 25\' error: unexpected INT at position 295 near INT"}'

[sajdre]

Hey, @hassankhan, it was my mistake in MySQL syntax, the following works:

attendancePercent: {
      sql: `CONVERT(${CUBE}.\`Attendance percent\`, DECIMAL)`,
      type: `number`
},

Thanks!