Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

CAST( AS BINARY) affect on indexes?

  • 26 September 2017
  • 2 replies
  • 114 views
KieranM

Hi,


So my question is regarding the use of the below method of making a joining dimension case sensitive and whether it affects the naming convention used within the indexes field within a View?


dimension: will_be_case_sensitive {

sql: CAST(${TABLE}.something AS BINARY) ;;

}


I am trying to diagnose the results from a DESCRIBE query within the SQL Runner. My indexes do not appear to be getting passed through this binary join…


Thanks!

2 replies

rufus
Rank
Userlevel 3

@KieranM I’m not sure casting the field as binary within the dimension itself is the best approach here.


Depending on the database it may be better to apply the binary conversion in the join so that the indexes in the derived table are not affected. Something like:


SELECT fields

FROM A

JOIN B ON BINARY A.something = B.something ;;

It’s also worth keeping in mind that in the binary form, accented letters would not match the unaccented form so this may not work as expected. Another approach would be to use a collation. Something like:


SELECT fields

FROM A

JOIN B ON A.Code = B.Code COLLATE latin1_bin ;;

The exact solution here is going to depend on the overall aim and the particular database we’re working with. Feel free to visit us at help.looker.com and we can take a further look if you’re still having issues.

KieranM

Thanks @rufus for the response. I will try with using BINARY in the LookML join sql_on: statement and see if that works. Failing that, I will send an email to support.


Thanks

Reply


Terms of Service