LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (August 2006, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Thu, 3 Aug 2006 06:27:10 -0700
Reply-To:     "jessgraves1@gmail.com" <jessgraves1@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "jessgraves1@gmail.com" <jessgraves1@GMAIL.COM>
Organization: http://groups.google.com
Subject:      JMP: Variable SQL statements in Open Database call
Comments: To: sas-l@uga.edu
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 this.

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 Eval/Expr magic.

// 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 Substitute. snt=Open Database( "DSN=MS Access Database;DBQ=C:\work2\blah\JMP\Detector.mdb;DefaultDir=C:\work2\blah\JMP;DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\MS Access Database (not sharable).dsn;MaxBufferSize=2048;PageTimeout=5;UID=admin;", sss, // <= gets substituted "snt" ) ), Expr(sss), // <= gets jsql jsql ) );

Hope it helps somebody.

Jess


Back to: Top of message | Previous page | Main SAS-L page