|Date: ||Wed, 27 Feb 2002 13:33:51 -0500|
|Reply-To: ||Edward Heaton <HEATONE@WESTAT.COM>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||Edward Heaton <HEATONE@WESTAT.COM>|
|Subject: ||Re: sql monotonic|
|Content-Type: ||text/plain; charset="iso-8859-1"|
You are using the SQL Pass-through facility. It works like this.
* You make a connection to a database.
* You use SAS SQL to pass external-database-specific SQL code to the
external database. That's the part in your parentheses.
Below, you are using MONOTONIC(), which is SAS SQL code, where you are
supposed to be using your database-specific SQL code. You can fix this my
moving the WHERE statement to the external code. Try something like
Proc sql ;
Connect to odbc as myDb (
dataSrc="MVSDB2P" user=PDRSL password=XXXXXXXX
Create table acts as
select * from connection to myDb
( select * from DMDB.SIS_DEGREE_V )
where ( monotonic() le 10 )
I know that this will cause all of the data file to be transported from the
external database to SAS and then be evaluated, but I know of no other way
to use MONOTONIC() here. I don't know DB2; there might be a row-counter
variable in that system that you can use directly.
I am intently working on a proposal and do not have time to test the above
code. MONOTONIC() is not documented and there are some pecularities about
it, so you should be careful that you get what you want.
Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1550 Research Boulevard, Room 2018, Rockville, MD 20850-3195
Voice: (301) 610-4818 Fax: (301) 294-3992
From: Evan Davies [mailto:email@example.com]
Sent: Wednesday, February 27, 2002 12:39 PM
To: Edward Heaton
Subject: sql monotonic
Hi - I read about the monotonic criteria from your post on SAS-L. Can you
tell me what I'm doing wrong here invoking it? Thanks, Evan.
195 connect to odbc as mydb
196 (datasrc="MVSDB2P" user=PDRSL password=XXXXXXXX);
197 create table acts as
198 select * from connection to mydb
199 (select *
200 from DMDB.SIS_DEGREE_V
201 where ( monotonic() le 10) ) ;
ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0104N An unexpected
token ")" was found
following "". Expected tokens may include: "( + - ? : USER COUNT
<DECIMAL> <INTEGER> ". SQLSTATE=42601
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
real time 1.30 seconds
Office of Institutional Research
The College of William and Mary
Phone: 757 221-2147 FAX: 757 221-2080