772663_en-US

liliconcom

SQL Query Case Sensitivity Issue

I'm using JSL to retrieve data from a MySQL database. However, I've encountered an issue where the filtering conditions in my SQL query are case-insensitive by default. This means that the results returned are the same regardless of the case of the filter value.

Here are the two scripts that produce the same results:

Script 1:

New SQL Query(
Version( 130 ),
Connection(
"ODBC:DATABASE=my_database;DSN=my_dsn;PORT=3306;PWD=my_password;SERVER=localhost;UID=my_user;"
),
QueryName( "test_data" ),
Select(
Column( "Column1", "t1" ),
Column( "Column2", "t1" ),
Column( "Column3", "t1" )
),
From( Table( "test_data", Alias( "t1" ) ) ),
Where(
Not Or(
In List(
Column( "Column1", "t1" ),
{"A"},
UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
)
)
)
) << Run;

 

 

Script 2:

 

New SQL Query(
Version( 130 ),
Connection(
"ODBC:DATABASE=my_database;DSN=my_dsn;PORT=3306;PWD=my_password;SERVER=localhost;UID=my_user;"
),
QueryName( "test_data" ),
Select(
Column( "Column1", "t1" ),
Column( "Column2", "t1" ),
Column( "Column3", "t1" )
),
From( Table( "test_data", Alias( "t1" ) ) ),
Where(
Not Or(
In List(
Column( "Column1", "t1" ),
{"a"},
UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
)
)
)
) << Run;

 

How can I modify the New SQL Query function in my JSL scripts to make the filtering conditions case-sensitive?

 

Thank you for your help!

Automation and ScriptingRe: SQL Query Case Sensitivity Issue

Someone from JMP most likely knows better, but have you what the SQL query looks like? I think you can see it by converting to Custom SQL

 

Re: SQL Query Case Sensitivity Issue

Also how is your database configured? If you run your query in some other application do you get the same results? I'm not familiar with MySQL but maybe those are by default case insensitive https://dev.mysql.com/doc/refman/8.4/en/case-sensitivity.html

Re: SQL Query Case Sensitivity Issue

The SQL statement is similar to this:

SELECT t1.`Column1`, t1.`Column2`, t1.`Column3`
FROM test_data  t1 
WHERE  (  NOT  (  (  ( t1.`Column1` IN  ( 'A' )  )  )  )  ) ;

I know that adding the BINARY keyword in SQL can enforce case sensitivity, for example:

SELECT `Column1`, `Column2`, `Column3`
FROM `test_data` AS `t1`
WHERE BINARY `Column1` NOT IN ('A');

And using the open Database function, I can execute SQL statements directly. However, I am particularly interested in whether the New SQL Query function in JSL can achieve the same case-sensitive effect.

Thank you for your assistance!

Re: SQL Query Case Sensitivity Issue

I think you might have to add custom expression to handle situations like that

or

 

Re: SQL Query Case Sensitivity Issue

Got it, that would be similar to referencing SQL statements in open Database function. Thanks!