cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
jumboshrimps
jumboshrimps
Level I

custom expression Today() minus seven days in query builder

Apr 9, 2018 10:33 AM (6130 views)

Attempting to obtain all releases by date (reldate) 7 days ago from ORACLE through ODBC Connection table.

Code below returns the attached error image:

 

SELECT DISTINCT t1.PIN, t1.PCP, t1.LASTNAME, t1.COMDATE,
 t1.RELDATE, t1.JLOCAT, t1.SEX, t1.RELTYPE,
 t1.RELAUTH, t1.RELCUST
FROM schema.table  t1
WHERE  (  (  ( t1.RELDATE >= (Today() - In Days( 7 ))  )

 

2nd question:

Once I get this query straight, does JMP have an automated macro function to run this  jmpquery every Monday morning at 3:00 am?   - I  also have the JMP addon for Excel 2010. 

Preview file
19 KB
1 REPLY 1
pmroz
Super User pmroz
Super User

Re: custom expression Today() minus seven days in query builder

Apr 9, 2018 10:52 AM (6128 views)  |  Posted in reply to message from jumboshrimps 04-09-2018

You're missing two close parentheses.

SELECT DISTINCT t1.PIN, t1.PCP, t1.LASTNAME, t1.COMDATE,
 t1.RELDATE, t1.JLOCAT, t1.SEX, t1.RELTYPE,
 t1.RELAUTH, t1.RELCUST
FROM schema.table  t1
WHERE  (  (  ( t1.RELDATE >= (Today() - In Days( 7 ))  ) ) )

If you have Oracle errors try executing the query in a SQL tool like PL/SQL Developer, Toad etc.