GET DATA /Type=ODBC questions

[Dennis Deck]

1) I cannot figure out how to test for string values in the WHERE clause
of a SQL statement in a GET DATA /Type=ODBC command.  
For example, ' WHERE Fund = "SAPISP" ' generates a syntax error.    I
have tried many variations such as double and triple quotes, enclose
within { } [the v14 syntax manual describes {} syntax for date/time
parameters], enclose string within a string function, etc with no
success.

2) I have no trouble accessing either MS Access or MS SQL Server
databases directly using the GET DATA /Type=ODBC  wizard to generate the
preliminary syntax.
However, I have been unsuccessful when I try to point to an Access
database that serves as the front end to a SQL Server database via
linked tables.  The wizard only shows the available queries - not the
linked tables.  Furthermore, it generates syntax errors or crashes when
I try select a query and try to run the generated code.

Any suggestions on either issue?  

Thanks.

Dennis Deck, PhD
RMC Research Corporation
111 SW Columbia Street, Suite 1200
Portland, Oregon 97201-5843
voice: 503-223-8248 x715
voice: 800-788-1887 x715
fax:  [phone]
[hidden email]

[Dennis Deck]

I finally answered my first question - how to test for a string value in
a WHERE clause.  The following is valid:   ' WHERE Fund=''SAPISP'' '

Double single quotes ('') works while double double quotes ("") does
not.

Dennis Deck, PhD
RMC Research Corporation
[hidden email]

-----Original Message-----
From: Simon Freidin [mailto:[hidden email]]
Sent: Thursday, August 03, 2006 8:11 PM
To: Dennis Deck
Subject: Re: GET DATA /Type=ODBC questions

At 12:33 PM 4/08/2006, you wrote:
>1) I cannot figure out how to test for string values in the WHERE
clause
>of a SQL statement in a GET DATA /Type=ODBC command.
>For example, ' WHERE Fund = "SAPISP" ' generates a syntax error.    I
>have tried many variations such as double and triple quotes, enclose
>within { } [the v14 syntax manual describes {} syntax for date/time
>parameters], enclose string within a string function, etc with no
>success.

the problem is in the columnname ?
' select * from tablename where tablename.fund = "SAPISP" '
' select * from tablename where [fund] = "SAPISP" '
If this isn't it, post the complete get data command.

>2) I have no trouble accessing either MS Access or MS SQL Server
>databases directly using the GET DATA /Type=ODBC  wizard to generate
the
>preliminary syntax.
>However, I have been unsuccessful when I try to point to an Access
>database that serves as the front end to a SQL Server database via
>linked tables.  The wizard only shows the available queries - not the
>linked tables.  Furthermore, it generates syntax errors or crashes when
>I try select a query and try to run the generated code.

The front-end would be a problem as the linked tables are only generated
at
access run time?
Either read the SQL database or save the tables generated from the
access
SQL database queries into an access table in a new  access database that

will be SPSS accessible?

cheers
Simon

>Any suggestions on either issue?
>
>Thanks.
>
>Dennis Deck, PhD
>RMC Research Corporation
>111 SW Columbia Street, Suite 1200
>Portland, Oregon 97201-5843
>voice: 503-223-8248 x715
>voice: 800-788-1887 x715
>fax:  [phone]
>[hidden email]

Research Database Manager and Analyst
Melbourne Institute of Applied Economic and Social Research
The University of Melbourne
Melbourne VIC 3010 Australia
New Tel: [phone] New Fax: [phone]
http://www.melbourneinstitute.com/hilda/

[Oliver, Richard]

Different databases have different rule for quoted strings, and this issue can be complicated by the fact that the SQL statement within SPSS also needs to be quoted. As a general rule, the Database Wizard will produce the correct quoting for the particular database type, and you can paste syntax from the wizard to see what it does for your database, although that can sometimes look a little arcane.

It would appear that 'WHERE stringvar="string value" '

does not work in Access, although it does work in some other databases. As you discovered, doubling the single quotes will work in Access, and single-quoting within a double-quoted SQL statement will also work:

"WHERE stringvar='string value' "

And that is what the wizard would paste for Access.

The rules may be subtly different, depending on the database. Actual mileage may vary. As an almost famous UI designer once said, "Standards must be good, because there are so many of them."

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Dennis Deck
Sent: Thursday, August 03, 2006 10:22 PM
To: [hidden email]
Subject: Re: GET DATA /Type=ODBC questions

I finally answered my first question - how to test for a string value in
a WHERE clause.  The following is valid:   ' WHERE Fund=''SAPISP'' '

Double single quotes ('') works while double double quotes ("") does not.

Dennis Deck, PhD
RMC Research Corporation
[hidden email]

-----Original Message-----
From: Simon Freidin [mailto:[hidden email]]
Sent: Thursday, August 03, 2006 8:11 PM
To: Dennis Deck
Subject: Re: GET DATA /Type=ODBC questions

At 12:33 PM 4/08/2006, you wrote:
>1) I cannot figure out how to test for string values in the WHERE
clause
>of a SQL statement in a GET DATA /Type=ODBC command.
>For example, ' WHERE Fund = "SAPISP" ' generates a syntax error.    I
>have tried many variations such as double and triple quotes, enclose
>within { } [the v14 syntax manual describes {} syntax for date/time
>parameters], enclose string within a string function, etc with no
>success.

the problem is in the columnname ?
' select * from tablename where tablename.fund = "SAPISP" '
' select * from tablename where [fund] = "SAPISP" '
If this isn't it, post the complete get data command.

>2) I have no trouble accessing either MS Access or MS SQL Server
>databases directly using the GET DATA /Type=ODBC  wizard to generate
the
>preliminary syntax.
>However, I have been unsuccessful when I try to point to an Access
>database that serves as the front end to a SQL Server database via
>linked tables.  The wizard only shows the available queries - not the
>linked tables.  Furthermore, it generates syntax errors or crashes when
>I try select a query and try to run the generated code.

The front-end would be a problem as the linked tables are only generated at access run time?
Either read the SQL database or save the tables generated from the access SQL database queries into an access table in a new  access database that

will be SPSS accessible?

cheers
Simon

>Any suggestions on either issue?
>
>Thanks.
>
>Dennis Deck, PhD
>RMC Research Corporation
>111 SW Columbia Street, Suite 1200
>Portland, Oregon 97201-5843
>voice: 503-223-8248 x715
>voice: 800-788-1887 x715
>fax:  [phone]
>[hidden email]

Research Database Manager and Analyst
Melbourne Institute of Applied Economic and Social Research The University of Melbourne Melbourne VIC 3010 Australia New Tel: [phone] New Fax: [phone] http://www.melbourneinstitute.com/hilda/