cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
MeatPopsicle
Level I

Filter in query builder contains 102 different strings

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%' ) ) );

5 REPLIES 5

Re: Filter in query builder contains 102 different strings

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

Chris Kirchberg, M.S.2
Data Scientist, Life Sciences - Global Technical Enablement
JMP Statistical Discovery, LLC. - Denver, CO
Tel: +1-919-531-9927 ▪ Mobile: +1-303-378-7419 ▪ E-mail: chris.kirchberg@jmp.com
www.jmp.com
pmroz
Super User

Re: Filter in query builder contains 102 different strings

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?

MeatPopsicle
Level I

Re: Filter in query builder contains 102 different strings

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?

 

 

pmroz
Super User

Re: Filter in query builder contains 102 different strings

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

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.