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