Mark,
Pardon me asking if you have already done so, but I'm assuming you've
already set up your odbc drivers relevant to your shop (oracle, sql,
etc) and that you have permissions for table access. The reason I ask
is that I do not see your dsn connect strings with your syntax. Have
you tried the database wizard? Personally I do not like the database
wizard since it is hard to use if you have large and complex sql
statements. Most of the time we just want to copy an sql statement from
a current production run into SPSS and the GET DATA syntax the wizard
provides requires extensive use of " , +. The old GET CAPTURE syntax
makes it possible to just copy the sql statement. The wizard is still
useful in obtaining the DSN connect string which will place in syntax
your ID, password and server. Listed below is the GETCAPTURE syntax.
In terms of your sql, I would suggest testing your sql statement outside
of spss, for example, in our shop, we run the sql in DBVisualizer or
Oracle to work out the sql bugs, then cut/paste the sql into a GET
CAPTURE spss syntax file.
GET CAPTURE odbc
/CONNECT="DSN=PROD;UID=yourid;PWD=%*&^#(*;SRVR+PROD"
/SELECT place your sql statement here, no need for " , + to enclose
multiple lines
CACHE.
EXECUTE.
On Mon, Jun 14, 2010 at 10:26 AM,
[hidden email] wrote:
> I am working on a project that pulls very large datasets using SQL
> Server 2008 and then analyzes the data in SPSS. In the past, we have
> pulled the data to a text file, then imported into SPSS, but we'd like
> to skip the intermediate step.
>
> I've succeeded in doing a very simple data pull, but I'm having
> trouble getting it to do exactly the pull we want. I think that the
> quotes around the 'FlexValue' variable names may be messing things up.
> My questions are:
>
> Is it possible to run the following SQL query in SPSS?
> and
> What is the syntax for doing so?
>
> Thanks in advance,
>
> Mark
>
> SELECT EventDateTime,
> 'FlexValue1'= REPLACE(REPLACE(REPLACE(FlexValue1, CHAR(9),'
> '), CHAR(10),' '), CHAR(13),' ')
> WHERE SSID = 39
> and EventDateTime >= '04/23/2010 00:00:00'
> and EventDateTime
=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD
Locked
