Date: Thu, 3 Aug 2006 06:27:10 -0700
Reply-To: "firstname.lastname@example.org" <jessgraves1@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "email@example.com" <jessgraves1@GMAIL.COM>
Subject: JMP: Variable SQL statements in Open Database call
Content-Type: text/plain; charset="iso-8859-1"
A while back I posted a question about variable SQL statements in the
Open Database command in JSL. Seems that you can't use variables in
the Open Database command and so your SQL query is static.
Despite a helpful SAS guy, nobody had the answer. 3 mos later I found
the answer when I found that you can't use variables for the control
limits in a control chart in JMP 5.0.1a. The fix for that works for
I don't know why JMP won't evaluate a variable in an Open Database
command but here's the work around. You use Substitute with some
// SQL statement built at runtime.
jsql = "SELECT r.detector, r.status, r.peaking, r.le_ratio, r.fwhm,
r.spillover, d.serial_no, r.he_ratio FROM Results as r, Detector as d
WHERE d.serial_no LIKE '%" || workorder || "%' AND d.detector_handle =
r.Detector_handle" || searchFailed;
Eval( // to unpack the Expr's
Substitute( // to change sss to the SQL statement we want
Expr( // to delay execution of the Open Database cmd until after
Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\MS
Access Database (not
sss, // <= gets substituted
Expr(sss), // <= gets jsql
Hope it helps somebody.