editing sql in spss

[Keval Khichadia]

Hi,

I am writing syntax in Microsoft SQL Server and trying to extract data by pasting that into SPSS. If I paste the syntax without modification into SPSS soemtimes it doesn't run.

For example in Microsoft SQL Server this works,

SELECT T100.ID,    
T100.MAJOR,    
T100.TERM    
FROM ACADEMIA T100   
WHERE T100.TERM = 'FALL'   
 

In SPSS, I have to put 3 quotation marks (below) for the term Fall for the syntax to run, if I only enclose it with one I get an error message

/SQL = 'SELECT T100.ID,     '
'T100.MAJOR,     '
'T100.TERM     '
'FROM ACADEMIA T100    '
'WHERE T100.TERM = '''FALL'''     '
 
There are many other examples were this has happened esspecially when I am trying to do joins. Is there specific syntax that I have to use writing SQL statements in SPSS or is it specific to the server (Microsoft SQL Server) I am extracting data from?
 
 
As another example In Microsoft SQL Server this works,
 
select T100.ID,
            T100.MAJOR,
            T100.TERM,
            T100.YEAR,
            T200.LASTNAME,
            T200.FIRSTNAME
 
FROM ACADEMIA T100
 
LEFT JOIN IDENTIFICATION T200 ON T200.ID = T100.ID
 
but in SPSS I have to write it like this to work,
 
/SQL = 'select T100.ID,    '
            'T100.MAJOR,    '
            'T100.TERM,        '
            'T100.YEAR,        '
            'T200.LASTNAME,    '
            'T200.FIRSTNAME FROM   '
 '{oj ACADEMIA T100 LEFT OUTER JOIN IDENTIFICATION T200 ON T200.ID = T100.ID}'
 
Thanks,
 
Keval




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

[Peck, Jon]

SPSS just passes your SQL to the driver for the data source.  Sometimes the driver doesn't support all the functionality you would have with direct use of the data source, though.  The examples below look pretty simple, so I would expect the driver to support them.

Since the SQL has to be treated as a literal by the SPSS parser, it has to be in quotes.  If you use double quotes (") in your example below, you don't have to triple the single quotes in the SQL.  If you use the same type of outer quote as those inside, SPSS will take the doubled (not double) quote and single it up.  That is true whichever outer quote form you use.

One trap in writing your own SQL is that the lines are joined together when passed on to the driver without any extra punctuation, so it's a good idea to leave a space inside the quotes at the end of each line.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Keval Khichadia
Sent: Thursday, October 30, 2008 10:49 AM
To: [hidden email]
Subject: [SPSSX-L] editing sql in spss

Hi,

I am writing syntax in Microsoft SQL Server and trying to extract data by pasting that into SPSS. If I paste the syntax without modification into SPSS soemtimes it doesn't run.

For example in Microsoft SQL Server this works,

SELECT T100.ID,
T100.MAJOR,
T100.TERM
FROM ACADEMIA T100
WHERE T100.TERM = 'FALL'


In SPSS, I have to put 3 quotation marks (below) for the term Fall for the syntax to run, if I only enclose it with one I get an error message

/SQL = 'SELECT T100.ID,     '
'T100.MAJOR,     '
'T100.TERM     '
'FROM ACADEMIA T100    '
'WHERE T100.TERM = '''FALL'''     '

There are many other examples were this has happened esspecially when I am trying to do joins. Is there specific syntax that I have to use writing SQL statements in SPSS or is it specific to the server (Microsoft SQL Server) I am extracting data from?


As another example In Microsoft SQL Server this works,

select T100.ID,
            T100.MAJOR,
            T100.TERM,
            T100.YEAR,
            T200.LASTNAME,
            T200.FIRSTNAME

FROM ACADEMIA T100

LEFT JOIN IDENTIFICATION T200 ON T200.ID = T100.ID

but in SPSS I have to write it like this to work,

/SQL = 'select T100.ID,    '
            'T100.MAJOR,    '
            'T100.TERM,        '
            'T100.YEAR,        '
            'T200.LASTNAME,    '
            'T200.FIRSTNAME FROM   '
 '{oj ACADEMIA T100 LEFT OUTER JOIN IDENTIFICATION T200 ON T200.ID = T100.ID}'

Thanks,

Keval




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