User Community
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: 
  • DownloadSemiconductor Toolkit: Tools to create wafer maps, add wafer geometry to graphics, explore die defects & compare wafers.
  • Discovery Summit 2026: Early User Edition - September 23-24.Register. It's free.
  • Use JMP Clinical with CDISC-compliant data. Review studies, ID safety and efficacy signals & communicate findings with interactive graphics. Register to see how. Aug. 27, 2 pm US ET.

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
jumboshrimps
jumboshrimps
Level I

custom expression Today() minus seven days in query builder

Apr 9, 2018 10:33 AM (7424 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 (7422 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.

Recommended Articles