Futher Clarification on SPSS multiple SQL outer joins

[Chris Fisher-2]

So, I've copied my attempt at using Hal9000's approach, and SPSS says it's
not valid, so I'm pasting the syntax. I would greatly appreciate anyone's
help in determining if it's possible to work around SPSS's ridiculously
built-in limit of two tables if doing even one outer join. Thanks in
advance.


GET DATA /TYPE=ODBC /CONNECT=
'DSN=*******;UID=*******;PWD=********'
/SQL =
"SELECT T649.BOOKNO, T649.ORDRNO, T649.COUNTY, T632.BOOKNO AS BOOKNO1,
T632.FACLTY, T743.BOOKNO AS BOOKNO12, T743.INMTID, T743.DTBOOK FROM"
" { oj NYJJISDB.IK"
" T743 LEFT OUTER JOIN NYJJISDB.DD T649 ON T743.BOOKNO = T649.BOOKNO } WHERE
"
" T632.BOOKNO = T649.BOOKNO"

/ASSUMEDSTRWIDTH=255
CACHE.
EXECUTE.


--
Christopher Fisher, Ph.D.
Adjunct Professor
John Jay College of Criminal Justice
555 West 57th Street, Suite 605
New York, New York 10019
212-237-8000 (x2145)
212-237-8644 (fax)

=====================
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

[Marks, Jim]

Chris:

The workaround is to extract the tables, then use MATCH FILES to create
the outer joins

*** sample data.
data list free /id (f8.0) name (a8).
begin data
1 bill 2 sally 3 jim 4 kelly 5 ' '
6 will 7 pam 8 mike 9 alice
END DATA.
dataset name T1.

data list free /id (f8.0) car (a8).
begin data
1 dodge 2 ford 3 toyota 5 honda 6 saturn 8 hyundai
END DATA.
dataset name T2.

data list free /id (f8.0) city (a15).
begin data
1 vancouver 2 sanfran 3 bakersfield 5 reno 6 portland 7 richmond 9
alameda
END DATA.
dataset name T3.

dataset activate T1.

match files file = * /table = t2 /table = t3 /by id.
execute.

Note: in your datasets, bookno must have the same format in each table.
Each dataset must be sorted in ascending order of bookno.

--jim


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Chris Fisher
Sent: Monday, December 10, 2007 3:38 PM
To: [hidden email]
Subject: Futher Clarification on SPSS multiple SQL outer joins

So, I've copied my attempt at using Hal9000's approach, and SPSS says
it's not valid, so I'm pasting the syntax. I would greatly appreciate
anyone's help in determining if it's possible to work around SPSS's
ridiculously built-in limit of two tables if doing even one outer join.
Thanks in advance.


GET DATA /TYPE=ODBC /CONNECT=
'DSN=*******;UID=*******;PWD=********'
/SQL =
"SELECT T649.BOOKNO, T649.ORDRNO, T649.COUNTY, T632.BOOKNO AS BOOKNO1,
T632.FACLTY, T743.BOOKNO AS BOOKNO12, T743.INMTID, T743.DTBOOK FROM"
" { oj NYJJISDB.IK"
" T743 LEFT OUTER JOIN NYJJISDB.DD T649 ON T743.BOOKNO = T649.BOOKNO }
WHERE "
" T632.BOOKNO = T649.BOOKNO"

/ASSUMEDSTRWIDTH=255
CACHE.
EXECUTE.


--
Christopher Fisher, Ph.D.
Adjunct Professor
John Jay College of Criminal Justice
555 West 57th Street, Suite 605
New York, New York 10019
212-237-8000 (x2145)
212-237-8644 (fax)

=====================
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

=====================
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

[Hal 9000]

Chris,

In practice, I tend to use Jim's approach...it's more interactive, and
easier to construct on the fly verses a good sql statement. I like
having options, though...

FYI, I ran this successfully from within SPSS (version 9.0!) upon the
test database in MSAccess 2003

*Copied and pasted verbatim.

GET CAPTURE ODBC
        /CONNECT='DSN=MS Access Database;DBQ=C:\Documents and'+
 ' Settings\default\My Documents\Test db.mdb;DefaultDir=C:\Documents and'+
 ' Settings\default\My Documents;DriverId=25;FIL=MS'+
 ' Access;MaxBufferSize=2048;PageTimeout=5;'
        /SQL = 'SELECT '
        ' a.id as id, '
        ' a.f_name as f_name, '
        ' b.car as car, '
        ' c.city as city '
        ' from (t1 a left outer join T2 b on b.id = a.id) '
        ' left outer join T3 c on c.id = a.id; '.
exe.

Hope that helps!
-Gary

On Dec 10, 2007 3:02 PM, Marks, Jim <[hidden email]> wrote:

> Chris:
>
> The workaround is to extract the tables, then use MATCH FILES to create
> the outer joins
>
> *** sample data.
> data list free /id (f8.0) name (a8).
> begin data
> 1 bill 2 sally 3 jim 4 kelly 5 ' '
> 6 will 7 pam 8 mike 9 alice
> END DATA.
> dataset name T1.
>
> data list free /id (f8.0) car (a8).
> begin data
> 1 dodge 2 ford 3 toyota 5 honda 6 saturn 8 hyundai
> END DATA.
> dataset name T2.
>
> data list free /id (f8.0) city (a15).
> begin data
> 1 vancouver 2 sanfran 3 bakersfield 5 reno 6 portland 7 richmond 9
> alameda
> END DATA.
> dataset name T3.
>
> dataset activate T1.
>
> match files file = * /table = t2 /table = t3 /by id.
> execute.
>
> Note: in your datasets, bookno must have the same format in each table.
> Each dataset must be sorted in ascending order of bookno.
>
> --jim
>
>
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
> Chris Fisher
> Sent: Monday, December 10, 2007 3:38 PM
> To: [hidden email]
> Subject: Futher Clarification on SPSS multiple SQL outer joins
>
> So, I've copied my attempt at using Hal9000's approach, and SPSS says
> it's not valid, so I'm pasting the syntax. I would greatly appreciate
> anyone's help in determining if it's possible to work around SPSS's
> ridiculously built-in limit of two tables if doing even one outer join.
> Thanks in advance.
>
>
> GET DATA /TYPE=ODBC /CONNECT=
> 'DSN=*******;UID=*******;PWD=********'
> /SQL =
> "SELECT T649.BOOKNO, T649.ORDRNO, T649.COUNTY, T632.BOOKNO AS BOOKNO1,
> T632.FACLTY, T743.BOOKNO AS BOOKNO12, T743.INMTID, T743.DTBOOK FROM"
> " { oj NYJJISDB.IK"
> " T743 LEFT OUTER JOIN NYJJISDB.DD T649 ON T743.BOOKNO = T649.BOOKNO }
> WHERE "
> " T632.BOOKNO = T649.BOOKNO"
>
> /ASSUMEDSTRWIDTH=255
> CACHE.
> EXECUTE.
>
>
> --
> Christopher Fisher, Ph.D.
> Adjunct Professor
> John Jay College of Criminal Justice
> 555 West 57th Street, Suite 605
> New York, New York 10019
> 212-237-8000 (x2145)
> 212-237-8644 (fax)
>
> =====================
> 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
>
> =====================
> 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
>

=====================
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

[Hal 9000]

Chris,

SPSS automatically - it appears - creates table aliases such as
"T746"...those can be replaced by anything you prefer. I like a,b,c,
etc.

Note: I only see 2 tables in this query...didn't you want to do multiple oj's?

Rephrased, your query would be:

GET DATA
  /TYPE=ODBC
  /CONNECT= 'DSN=*******;UID=*******;PWD=********'
  /SQL = ' select '
    ' b.BOOKNO, '
    ' b.ORDRNO, '
    ' b.COUNTY, '
    ' b.BOOKNO as BOOKNO1, '
    ' b.FACLTY, '
    ' a.BOOKNO as BOOKNO12, '
    ' a.INMTID, '
    ' a.DTBOOK '
    ' from NYJJISDB.IK a left outer join NYJJISDB.DD b on b.BOOKNO = a.BOOKNO; '
  /ASSUMEDSTRWIDTH=255.
cache.
exe.

Again, Jim's approach is better in that you can see more clearly
what's happening at intermediate steps, and after all, the match files
command is powerful. Much is made of (and rightfully so) the use of
Python with SPSS, but I suspect that the average user gets more value
from learning SQL before taking on Python.
-Gary

=====================
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