SPSSX Discussion

SQL_ODBC + SPSS16

Classic

List

Threaded

4 messages Options

Khaleel Hussaini

SQL_ODBC + SPSS16

Hi! Users,
I wanted to know if anyone is familiar with this sql error as I want to
retrieve cases starting from July 1st of 2001. The syntax is given below,
the error message I get is expected date but invalid character. How can I
limit the cases for a calendar year?

GET DATA
/TYPE=ODBC

/CONNECT='DSN=HDD;UID=hussais;PWD=$p#V-Q.#(#-);DBQ=natp;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC='+

'10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC='+
'F;FBS=64000;TLO=0;'
/SQL="SELECT T0.CLI0_PK, T0.CLI0_LAST_NAME, T0.CLI0_FIRST_NAME, T0.SEX,
T0.CLI0_BIRTH_DATE, "+
"T0.CLI0_RACE_FK, T0.CLI0_ETHN_FK, T1.DGN0_CLI0_FK, T1.DGN0_ICDC_FK,
T1.DGN0_ICD5_FK, "+
"T1.DGN0_OP00_FK, T2.VST0_CLI0_FK, T2.VISIT_DATE, T2.VST0_FAC0_TO_FK,
T2.VST0_FAC0_FR_FK, "+
"T2.VST0_VSTT_FK, T2.VST0_SS00_FK, T2.VST0_BRF0_FK, T2.VST0_OP00_FK
FROM "+
"H90CATS.H90CLIENT_GUARDIAN T0, H90CATS.H90DIAGNOSIS T1,
H90CATS.H90VISITS T2 WHERE "+
"T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK = T2.VST0_CLI0_FK and
T0.CLI0_PK = "+
"T2.VST0_CLI0_FK and T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK =
T2.VST0_CLI0_FK and "+
"T0.CLI0_PK = T2.VST0_CLI0_FK and (T2.VISIT_DATE > {'2006-07-01'})"
/ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
DATASET NAME DataSet5 WINDOW=FRONT.

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

Oliver, Richard

Re: SQL_ODBC + SPSS16

Date literals have to specified in a very specific manner. From the SPSS Command Syntax Reference:

If the SQL contains WHERE clauses with expressions for case selection, dates and times in expressions need to be specified in a special manner (including the curly braces shown in the examples):

* Date literals should be specified using the general form {d 'yyyy-mm-dd'}.

* Time literals should be specified using the general form {t 'hh:mm:ss'}.

* Date/time literals (timestamps) should be specified using the general form {ts 'yyyy-mm-dd hh:mm:ss'}.

* The entire date and/or time value must be enclosed in single quotes. Years must be expressed in four-digit form, and dates and times must contain two digits for each portion of the value. For example January 1, 2005, 1:05 AM would be expressed as:

{ts '2005-01-01 01:05:00'}

Note the letter d before the single-quoted date example above.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Khaleel Hussaini
Sent: Wednesday, February 13, 2008 11:48 AM
To: [hidden email]
Subject: SQL_ODBC + SPSS16

Hi! Users,
I wanted to know if anyone is familiar with this sql error as I want to
retrieve cases starting from July 1st of 2001. The syntax is given below,
the error message I get is expected date but invalid character. How can I
limit the cases for a calendar year?

GET DATA
/TYPE=ODBC

/CONNECT='DSN=HDD;UID=hussais;PWD=$p#V-Q.#(#-);DBQ=natp;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC='+

'10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC='+
'F;FBS=64000;TLO=0;'
/SQL="SELECT T0.CLI0_PK, T0.CLI0_LAST_NAME, T0.CLI0_FIRST_NAME, T0.SEX,
T0.CLI0_BIRTH_DATE, "+
"T0.CLI0_RACE_FK, T0.CLI0_ETHN_FK, T1.DGN0_CLI0_FK, T1.DGN0_ICDC_FK,
T1.DGN0_ICD5_FK, "+
"T1.DGN0_OP00_FK, T2.VST0_CLI0_FK, T2.VISIT_DATE, T2.VST0_FAC0_TO_FK,
T2.VST0_FAC0_FR_FK, "+
"T2.VST0_VSTT_FK, T2.VST0_SS00_FK, T2.VST0_BRF0_FK, T2.VST0_OP00_FK
FROM "+
"H90CATS.H90CLIENT_GUARDIAN T0, H90CATS.H90DIAGNOSIS T1,
H90CATS.H90VISITS T2 WHERE "+
"T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK = T2.VST0_CLI0_FK and
T0.CLI0_PK = "+
"T2.VST0_CLI0_FK and T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK =
T2.VST0_CLI0_FK and "+
"T0.CLI0_PK = T2.VST0_CLI0_FK and (T2.VISIT_DATE > {'2006-07-01'})"
/ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
DATASET NAME DataSet5 WINDOW=FRONT.

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

Marks, Jim

Re: SQL_ODBC + SPSS16

In reply to this post by Khaleel Hussaini

try this:
GET DATA
/TYPE=ODBC /CONNECT = yourconnect
/SQL = "SELECT your SQL"
/"WHERE
"T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK = T2.VST0_CLI0_FK and
T0.CLI0_PK = "+
"T2.VST0_CLI0_FK and T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK =
T2.VST0_CLI0_FK and "+
"T0.CLI0_PK = T2.VST0_CLI0_FK and (T2.VISIT_DATE > d
{'2006-07-01'})"
/ASSUMEDSTRWIDTH=255.

I added a "d" in front of the { } braces.

--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Khaleel Hussaini
Sent: Wednesday, February 13, 2008 11:48 AM
To: [hidden email]
Subject: SQL_ODBC + SPSS16

Hi! Users,
I wanted to know if anyone is familiar with this sql error as I want
to retrieve cases starting from July 1st of 2001. The syntax is given
below, the error message I get is expected date but invalid character.
How can I limit the cases for a calendar year?

GET DATA
/TYPE=ODBC

/CONNECT='DSN=HDD;UID=hussais;PWD=$p#V-Q.#(#-);DBQ=natp;DBA=W;APA=T;EXC=
F;FEN=T;QTO=T;FRC='+

'10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS
=T;MDI=F;CSR=F;FWC='+
'F;FBS=64000;TLO=0;'
/SQL="SELECT T0.CLI0_PK, T0.CLI0_LAST_NAME, T0.CLI0_FIRST_NAME,
T0.SEX, T0.CLI0_BIRTH_DATE, "+
"T0.CLI0_RACE_FK, T0.CLI0_ETHN_FK, T1.DGN0_CLI0_FK, T1.DGN0_ICDC_FK,
T1.DGN0_ICD5_FK, "+
"T1.DGN0_OP00_FK, T2.VST0_CLI0_FK, T2.VISIT_DATE,
T2.VST0_FAC0_TO_FK, T2.VST0_FAC0_FR_FK, "+
"T2.VST0_VSTT_FK, T2.VST0_SS00_FK, T2.VST0_BRF0_FK, T2.VST0_OP00_FK
FROM "+
"H90CATS.H90CLIENT_GUARDIAN T0, H90CATS.H90DIAGNOSIS T1,
H90CATS.H90VISITS T2 WHERE "+
"T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK = T2.VST0_CLI0_FK and
T0.CLI0_PK = "+
"T2.VST0_CLI0_FK and T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK =
T2.VST0_CLI0_FK and "+
"T0.CLI0_PK = T2.VST0_CLI0_FK and (T2.VISIT_DATE > {'2006-07-01'})"
/ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
DATASET NAME DataSet5 WINDOW=FRONT.

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

Melissa Ives

Re: SQL_ODBC + SPSS16

In reply to this post by Khaleel Hussaini

Seems to me that if you want data "starting from July 1st of 2001"
you would want to use: "T2.VISIT_DATE > d {'2001-07-01'}" instead of
"T2.VISIT_DATE > d {'2006-07-01'}"

Unless you meant that you wanted data starting from July 1st of 2006?

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Marks, Jim
Sent: Wednesday, February 13, 2008 1:02 PM
To: [hidden email]
Subject: Re: [SPSSX-L] SQL_ODBC + SPSS16

try this:
GET DATA
/TYPE=ODBC /CONNECT = yourconnect
/SQL = "SELECT your SQL"
/"WHERE
"T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK = T2.VST0_CLI0_FK and
T0.CLI0_PK = "+
"T2.VST0_CLI0_FK and T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK =
T2.VST0_CLI0_FK and "+
"T0.CLI0_PK = T2.VST0_CLI0_FK and (T2.VISIT_DATE > d
{'2006-07-01'})"
/ASSUMEDSTRWIDTH=255.

I added a "d" in front of the { } braces.

--jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Khaleel Hussaini
Sent: Wednesday, February 13, 2008 11:48 AM
To: [hidden email]
Subject: SQL_ODBC + SPSS16

Hi! Users,
I wanted to know if anyone is familiar with this sql error as I want
to retrieve cases starting from July 1st of 2001. The syntax is given
below, the error message I get is expected date but invalid character.
How can I limit the cases for a calendar year?

GET DATA
/TYPE=ODBC

/CONNECT='DSN=HDD;UID=hussais;PWD=$p#V-Q.#(#-);DBQ=natp;DBA=W;APA=T;EXC=
F;FEN=T;QTO=T;FRC='+

'10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS
=T;MDI=F;CSR=F;FWC='+
'F;FBS=64000;TLO=0;'
/SQL="SELECT T0.CLI0_PK, T0.CLI0_LAST_NAME, T0.CLI0_FIRST_NAME,
T0.SEX, T0.CLI0_BIRTH_DATE, "+
"T0.CLI0_RACE_FK, T0.CLI0_ETHN_FK, T1.DGN0_CLI0_FK, T1.DGN0_ICDC_FK,
T1.DGN0_ICD5_FK, "+
"T1.DGN0_OP00_FK, T2.VST0_CLI0_FK, T2.VISIT_DATE,
T2.VST0_FAC0_TO_FK, T2.VST0_FAC0_FR_FK, "+
"T2.VST0_VSTT_FK, T2.VST0_SS00_FK, T2.VST0_BRF0_FK, T2.VST0_OP00_FK
FROM "+
"H90CATS.H90CLIENT_GUARDIAN T0, H90CATS.H90DIAGNOSIS T1,
H90CATS.H90VISITS T2 WHERE "+
"T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK = T2.VST0_CLI0_FK and
T0.CLI0_PK = "+
"T2.VST0_CLI0_FK and T0.CLI0_PK = T1.DGN0_CLI0_FK and T0.CLI0_PK =
T2.VST0_CLI0_FK and "+
"T0.CLI0_PK = T2.VST0_CLI0_FK and (T2.VISIT_DATE > {'2006-07-01'})"
/ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
DATASET NAME DataSet5 WINDOW=FRONT.

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

PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at (309) 827-6026 for assistance.

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