SPSSX Discussion

ODBC error message

Classic

List

Threaded

5 messages Options

Raffe, Sydelle, SSA

ODBC error message

A frustrating SPSS day.

I used the wizard to generate the following simple code and got the resulting error message:

GET DATA /TYPE=ODBC /CONNECT=
'DSN=CIS01USER;UID=CIS01USER/CIS01USER;PWD=;DBQ=CIS01USER ;DBA=W;APA=T;EXC'+
'=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;'
'BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;'
/SQL = 'SELECT CS_ID, PGM_TYP_CD, RD_ID, DUE_DT, APPT_ID, RD_STS_CD,'+
' PKT_SENT_DT, PKT_RCV_DT, RD_STS_DT, MAIL_IN_DUE_DT,'
' GNRT_PKT_SW, APPT_REQR_SW, MAX_DUE_DT, DUE_DT_MOVE_IND, CRT_USR_ID'+
', CRT_DTM, UPD_USR_ID, UPD_DTM, HIST_IND, '
'LAST_RTRV_DT, CUR_REC_SW, PKT_RCV_CMP_SW FROM CIS01.RD WHERE (PGM_TYP'+
'_CD = PGM_TYP_CD MC )'
/ASSUMEDSTRWIDTH=255
.

>Warning. Command name: GET DATA
>SQLExecDirect failed :[Oracle][ODBC][Ora]ORA-00907: missing right parenthesis

CACHE. //May be removed but insures data integrity.

Sydelle Raffe, Alameda County Social Services Agency
Information Services Division, Office of Data Management
e:mail: [hidden email]
phone: 510-271-9174 fax: 510-271-9107
If you have a request for information, please submit an ODM request form at: https://alamedasocialservices.org/staff/support_services/statistics_and_reports/odm/index.cfm

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

Raffe, Sydelle, SSA

Re: ODBC error message

Tried numerous variations of the parentheses space changes -- no luck.

Without the selection criteria I'd get more than 20 million records. Not workable. But, thanks.

-----Original Message-----
From: Marks, Jim [mailto:[hidden email]]
Sent: Thursday, December 20, 2007 12:45 PM
To: Raffe, Sydelle, SSA
Subject: RE: ODBC error message

try
1) removing both left and right parentheses
2) adding a space after the right paren at the end of the last line of
the sysntax

Sometime the ODBC driver is funky.

Could you run this as the sql part of the command?
GET DATA /TYPE = ODBC /CONNECT = yourconnect
'/SQL = SELECT * FROM CIS01.RD '
/ASSUMEDSTRWIDTH = 255.

Then you could select the records you need.

jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Raffe, Sydelle, SSA
Sent: Thursday, December 20, 2007 2:32 PM
To: [hidden email]
Subject: ODBC error message

A frustrating SPSS day.

I used the wizard to generate the following simple code and got the
resulting error message:

GET DATA /TYPE=ODBC /CONNECT=
'DSN=CIS01USER;UID=CIS01USER/CIS01USER;PWD=;DBQ=CIS01USER
;DBA=W;APA=T;EXC'+ '=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;'

'BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO
=O;'
/SQL = 'SELECT CS_ID, PGM_TYP_CD, RD_ID, DUE_DT, APPT_ID,
RD_STS_CD,'+ ' PKT_SENT_DT, PKT_RCV_DT, RD_STS_DT, MAIL_IN_DUE_DT,'
' GNRT_PKT_SW, APPT_REQR_SW, MAX_DUE_DT, DUE_DT_MOVE_IND,
CRT_USR_ID'+ ', CRT_DTM, UPD_USR_ID, UPD_DTM, HIST_IND, '
'LAST_RTRV_DT, CUR_REC_SW, PKT_RCV_CMP_SW FROM CIS01.RD WHERE
(PGM_TYP'+ '_CD = PGM_TYP_CD MC )'
/ASSUMEDSTRWIDTH=255
.

>Warning. Command name: GET DATA
>SQLExecDirect failed :[Oracle][ODBC][Ora]ORA-00907: missing right
>parenthesis

CACHE. //May be removed but insures data integrity.

Sydelle Raffe, Alameda County Social Services Agency Information
Services Division, Office of Data Management
e:mail: [hidden email]
phone: 510-271-9174 fax: 510-271-9107
If you have a request for information, please submit an ODM request form
at:
https://alamedasocialservices.org/staff/support_services/statistics_and_
reports/odm/index.cfm

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

Peck, Jon

Re: ODBC error message

First, you don't need parentheses in your where clause, although they are perfectly legal.

The problem, though, is probably a syntax error in the where specification. It says

WHERE (PGM_TYP'+ '_CD = PGM_TYP_CD MC )

PGM_TYP_CD MC
is not a legal expression AFAIK.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Raffe, Sydelle, SSA
Sent: Thursday, December 20, 2007 1:51 PM
To: [hidden email]
Subject: Re: [SPSSX-L] ODBC error message

Tried numerous variations of the parentheses space changes -- no luck.

Without the selection criteria I'd get more than 20 million records. Not workable. But, thanks.

-----Original Message-----
From: Marks, Jim [mailto:[hidden email]]
Sent: Thursday, December 20, 2007 12:45 PM
To: Raffe, Sydelle, SSA
Subject: RE: ODBC error message

try
1) removing both left and right parentheses
2) adding a space after the right paren at the end of the last line of
the sysntax

Sometime the ODBC driver is funky.

Could you run this as the sql part of the command?
GET DATA /TYPE = ODBC /CONNECT = yourconnect
'/SQL = SELECT * FROM CIS01.RD '
/ASSUMEDSTRWIDTH = 255.

Then you could select the records you need.

jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Raffe, Sydelle, SSA
Sent: Thursday, December 20, 2007 2:32 PM
To: [hidden email]
Subject: ODBC error message

A frustrating SPSS day.

I used the wizard to generate the following simple code and got the
resulting error message:

GET DATA /TYPE=ODBC /CONNECT=
'DSN=CIS01USER;UID=CIS01USER/CIS01USER;PWD=;DBQ=CIS01USER
;DBA=W;APA=T;EXC'+ '=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;'

'BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO
=O;'
/SQL = 'SELECT CS_ID, PGM_TYP_CD, RD_ID, DUE_DT, APPT_ID,
RD_STS_CD,'+ ' PKT_SENT_DT, PKT_RCV_DT, RD_STS_DT, MAIL_IN_DUE_DT,'
' GNRT_PKT_SW, APPT_REQR_SW, MAX_DUE_DT, DUE_DT_MOVE_IND,
CRT_USR_ID'+ ', CRT_DTM, UPD_USR_ID, UPD_DTM, HIST_IND, '
'LAST_RTRV_DT, CUR_REC_SW, PKT_RCV_CMP_SW FROM CIS01.RD WHERE
(PGM_TYP'+ '_CD = PGM_TYP_CD MC )'
/ASSUMEDSTRWIDTH=255
.

>Warning. Command name: GET DATA
>SQLExecDirect failed :[Oracle][ODBC][Ora]ORA-00907: missing right
>parenthesis

CACHE. //May be removed but insures data integrity.

Sydelle Raffe, Alameda County Social Services Agency Information
Services Division, Office of Data Management
e:mail: [hidden email]
phone: 510-271-9174 fax: 510-271-9107
If you have a request for information, please submit an ODM request form
at:
https://alamedasocialservices.org/staff/support_services/statistics_and_
reports/odm/index.cfm

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

Raffe, Sydelle, SSA

Re: ODBC error message

Yeah -- that makes sense. The wizard accepted it so I thought it was ok.

-----Original Message-----
From: Peck, Jon [mailto:[hidden email]]
Sent: Thursday, December 20, 2007 1:22 PM
To: Raffe, Sydelle, SSA; [hidden email]
Subject: RE: Re: [SPSSX-L] ODBC error message

First, you don't need parentheses in your where clause, although they are perfectly legal.

The problem, though, is probably a syntax error in the where specification. It says

WHERE (PGM_TYP'+ '_CD = PGM_TYP_CD MC )

PGM_TYP_CD MC
is not a legal expression AFAIK.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Raffe, Sydelle, SSA
Sent: Thursday, December 20, 2007 1:51 PM
To: [hidden email]
Subject: Re: [SPSSX-L] ODBC error message

Tried numerous variations of the parentheses space changes -- no luck.

Without the selection criteria I'd get more than 20 million records. Not workable. But, thanks.

-----Original Message-----
From: Marks, Jim [mailto:[hidden email]]
Sent: Thursday, December 20, 2007 12:45 PM
To: Raffe, Sydelle, SSA
Subject: RE: ODBC error message

try
1) removing both left and right parentheses
2) adding a space after the right paren at the end of the last line of
the sysntax

Sometime the ODBC driver is funky.

Could you run this as the sql part of the command?
GET DATA /TYPE = ODBC /CONNECT = yourconnect
'/SQL = SELECT * FROM CIS01.RD '
/ASSUMEDSTRWIDTH = 255.

Then you could select the records you need.

jim

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Raffe, Sydelle, SSA
Sent: Thursday, December 20, 2007 2:32 PM
To: [hidden email]
Subject: ODBC error message

A frustrating SPSS day.

I used the wizard to generate the following simple code and got the
resulting error message:

GET DATA /TYPE=ODBC /CONNECT=
'DSN=CIS01USER;UID=CIS01USER/CIS01USER;PWD=;DBQ=CIS01USER
;DBA=W;APA=T;EXC'+ '=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;'

'BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO
=O;'
/SQL = 'SELECT CS_ID, PGM_TYP_CD, RD_ID, DUE_DT, APPT_ID,
RD_STS_CD,'+ ' PKT_SENT_DT, PKT_RCV_DT, RD_STS_DT, MAIL_IN_DUE_DT,'
' GNRT_PKT_SW, APPT_REQR_SW, MAX_DUE_DT, DUE_DT_MOVE_IND,
CRT_USR_ID'+ ', CRT_DTM, UPD_USR_ID, UPD_DTM, HIST_IND, '
'LAST_RTRV_DT, CUR_REC_SW, PKT_RCV_CMP_SW FROM CIS01.RD WHERE
(PGM_TYP'+ '_CD = PGM_TYP_CD MC )'
/ASSUMEDSTRWIDTH=255
.

>Warning. Command name: GET DATA
>SQLExecDirect failed :[Oracle][ODBC][Ora]ORA-00907: missing right
>parenthesis

CACHE. //May be removed but insures data integrity.

Sydelle Raffe, Alameda County Social Services Agency Information
Services Division, Office of Data Management
e:mail: [hidden email]
phone: 510-271-9174 fax: 510-271-9107
If you have a request for information, please submit an ODM request form
at:
https://alamedasocialservices.org/staff/support_services/statistics_and_
reports/odm/index.cfm

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

Raffe, Sydelle, SSA

Re: odbc error message

In reply to this post by Raffe, Sydelle, SSA

Solved -- used wizard again and got it ok as follows

DATASET ACTIVATE DataSet12.
SORT CASES BY
rrr_due (A) .
DATASET ACTIVATE DataSet13.
GET DATA /TYPE=ODBC /CONNECT=
'DSN=CIS01USER;UID=CIS01USER/CIS01USER;PWD=;DBQ=CIS01USER ;DBA=W;APA=T;EXC'+
'=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;'
'BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;'
/SQL = "SELECT CS_ID, PGM_TYP_CD, RD_ID, DUE_DT, APPT_ID, RD_STS_CD,"+
" PKT_SENT_DT, PKT_RCV_DT, RD_STS_DT, MAIL_IN_DUE_DT,"
" GNRT_PKT_SW, APPT_REQR_SW, MAX_DUE_DT, DUE_DT_MOVE_IND, CRT_USR_ID"+
", CRT_DTM, UPD_USR_ID, UPD_DTM, HIST_IND, "
"LAST_RTRV_DT, CUR_REC_SW, PKT_RCV_CMP_SW FROM CIS01.RD WHERE (PGM_TYP"+
"_CD = 'MC' )"
/ASSUMEDSTRWIDTH=255
.
CACHE. //May be removed but insures data integrity.

Sydelle Raffe, Alameda County Social Services Agency
Information Services Division, Office of Data Management
e:mail: [hidden email]
phone: 510-271-9174 fax: 510-271-9107
If you have a request for information, please submit an ODM request form at: https://alamedasocialservices.org/staff/support_services/statistics_and_reports/odm/index.cfm

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