topic Re: Filter in query builder contains 102 different strings in Discussions

Filter in query builder contains 102 different strings

MeatPopsicle — Wed, 11 Apr 2018 17:28:33 GMT

In SAS 9.1 I a had a script querying a medication table with a list of over 100 different medications.

If the list was especially long I would create a spreadsheet in Excel and import the list into the work space, write a script matching where a.description = b.description.

Neither function appears in JMP.

I cannot bring in an Excel file, cannot bring in another table, ONCE THE CONNECTION TO THE ORACLE SCHEMA IS ESTABLISHED IN QUERYBUILDER.

CAN ONLY BRING IN TABLES IN THAT SPECIFIC (I have over FIVE ORACLE schemas to query) SCHEMA. (ridiculous, just ridiculous)

Do I need to create over 100 filters on the drug description, one for each medication?

OR is there a way to concatenate each drug prescription into one long string and filter on that string?

QueryBilder code filtering just one (of over 100) medications below:

SELECT DISTINCT ID, DESCRIPTION, STARTDATE, STOPDATE
FROM SCHEMA.TABLE
WHERE ( ( ( (t1.DESCRIPTION LIKE '%ABILIFY%' ) ) );

Re: Filter in query builder contains 102 different strings

Chris_Kirchberg — Wed, 11 Apr 2018 18:42:02 GMT

Have you taken a look at the documentation for Filtering within Query Builder for something like this? Is that what you are looking for:

https://www.jmp.com/support/help/14/build-the-sql-query.shtml#623512

Chris

Re: Filter in query builder contains 102 different strings

pmroz — Wed, 11 Apr 2018 18:43:29 GMT

Perhaps you are running into a limitation of query builder. Can you create a SQL query that does what you want? If that's the case you can use EXECUTE SQL. You can query as many schemas as you want in a "regular" SQL query.

Can you provide more examples of your 100 medications? Can you use an Oracle IN LIST?

Re: Filter in query builder contains 102 different strings

MeatPopsicle — Wed, 11 Apr 2018 21:27:36 GMT

Did a global replace of '%, %' with (' & '), - pasted entire seletion into tiny custom expression filter window (which worked).

Yes, I can see the query in the SQL Preview window but I can't edit that.

Is there any way to edit the SQL in the Preview Window?

If I'm going to write (copy) such long SQL code, isn't there some way to edit it in QueryBuilder?

Re: Filter in query builder contains 102 different strings

pmroz — Thu, 12 Apr 2018 12:57:29 GMT

I don't use Query Builder for developing or executing SQL queries, because our queries tend to be long and complex. I use PL/SQL Developer; other tools include TOAD, SQL Navigator and SQL Developer. The last one is free from Oracle. I develop the query in PLSD, and when it's doing what I want I use execute SQL to run it:

sql = 
"SELECT f.foo, b.bar
   FROM myschema1.fubar f,
        myschema2.barski b
  WHERE f.foo = b.bar";
dbc = create database connection("<your connection string here>");
dt = execute sql(dbc, sql, "My Table");
close database connection(dbc);

Re: Filter in query builder contains 102 different strings

Bill_Worley — Thu, 12 Apr 2018 13:06:21 GMT

There is a way to build your own SQL in Query Builder. Go to the red hot spot by Query Name and and select Convert to Custom SQL.