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
WHERE ( ( ( (t1.DESCRIPTION LIKE '%ABILIFY%' ) ) );
Have you taken a look at the documentation for Filtering within Query Builder for something like this? Is that what you are looking for:
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?
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?
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);
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.
There are no labels assigned to this post.