Date:         Wed, 8 Sep 2004 21:52:08 -0400
Reply-To:     Richard Ristow <wrristow@mindspring.com>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         Richard Ristow <wrristow@mindspring.com>
Subject:      How much SQL will GET CAPTURE do?
Content-Type: text/plain; charset="us-ascii"; format=flowed

There may be documentation on this somewhere, but I don't see it in
anything I have for SPSS or Access.

An SPSS "GET CAPTURE" to read an Access table consists largely of SQL
code, which I know is processed through Microsoft's ODBC interface. I
append a working example.

I've been generating GET CAPTUREs from the wizard and modifying them,
and there's a lot I don't understand. (For example, quoted SQL items,
within the SPSS quoted strings which are the SQL code, apparently have
to be quoted with "`", not with "'".)

I have a number of instances of tables with the same structure of
fields, that I want to read concatenated into SPSS. I can do a GET
CAPTURE for each and an ADD FILES in SPSS, but it would be a lot easier
to do a single GET CAPTURE. Could, for example, one query from tables
in two databases, catenated, by

    [list of fields, as below]
    'FROM SELECT'
    '"C:\B_Client\Martha Lang\HVTIS 2002-07-22\2Q2002nmCo1-28nm"."Home
Visits".*'
    'UNION SELECT'
    '"C:\B_Client\Martha Lang\HVTIS 2002-07-22\2Q2002nmCo28-56nm"."Home
Visits".*'
    '"TBL"'
    .

Or would this not work, because the .MDB file has to be named in the
header, preceding the SQL code? Or for some other reason?

How about ORDER BY on the SQL, to save a SORT CASES in SPSS?

And, where would one find documentation on what the syntax and
semantics of this command are? The syntax manual is terse and
uninformative.

-Thanks,
  Richard

Appendix: Working GET CAPTURE code
----------------------------------
GET CAPTURE ODBC /CONNECT='DSN=MS Access 97 Database;'
   'DBQ=C:\B_Client\Martha Lang\HVTIS 2002-07-22\2Q2002nmCo1-28nm.mdb;'

   'DefaultDir=C:\B_Client\Martha Lang\Testing;'
   'DriverId=25;FIL=MS Access;
    MaxBufferSize=512;PageTimeout=5;'
    /SQL = 'SELECT '
    " 'Home vis'                          AS `ABRV_TBL`  ,"
    "`Tbl`.`FN - PROGRAM WITHIN COUNTY`   AS `PROGRAM`   ,"
    "`Tbl`.`FN - COUNTY CODE`             AS `COUNTY`    ,"
    "`Tbl`.`FAMILY NUMBER-CR`             AS `FAMILY#`   ,"
    "`Tbl`.`FN - PROGRAM WITHIN COUNTY`   AS `PROGRAM`   ,"
    "`Tbl`.`VISIT NUMBER`                 AS `VISIT#`    ,"
    "`Tbl`.`FAMILY NUMBER-CR`             AS `FAMILY#`   ,"
    "`Tbl`.`DenverScreenerPresent`        AS `HV219`      "
    "`Tbl`.`VISIT NUMBER`                 AS `VISIT#`    ,"
    "`Tbl`.`DenverScreenerPresent`        AS `HV219`      "
    'FROM '
    '"C:\B_Client\Martha Lang\HVTIS 2002-07-22\2Q2002nmCo1-28nm"."Home
Visits"'
    '"TBL"'
    .