SQL Date Query - Limit Retrieved Cases

[DKUKEC]

Dear List,

When I run the first syntax the database returns the desired cases; however, when I try to use a specific date, I get the following error.  I have searched the fine manual, SPSS help, and google, with no luck.  I am trying to figure out WHERE I'm going wrong with my date specific query?  The first syntax works, but the second does not.  Any suggestions would be much appreciated.

Thank you
Damir

*/* this works **************
 
GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=wedprod;UID=RESTORE_REPORTER;PWD=>x7~2P2R<W;Host='+
    'gcprodrpt.co.palm-beach.fl.us;Port=1521;SN=prdr.co.palm-beach.fl.us'
  /SQL='SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, DATE_OF_BIRTH, SEX, RACE_CODE, ETHINICITY, '+
    'PROGRAM_START, PROGRAM_END FROM RESTORE.CLIENT WHERE (PROGRAM_START <  PROGRAM_END)'
  /ASSUMEDSTRWIDTH=255.
 
CACHE.
EXECUTE.
 
DATASET NAME DATA1 WINDOW=FRONT


*/* this does not work *************************************************************

GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=wedprod;UID=RESTORE_REPORTER;PWD=>x7~2P2R<W;Host='+
    'gcprodrpt.co.palm-beach.fl.us;Port=1521;SN=prdr.co.palm-beach.fl.us'
  /SQL='SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, DATE_OF_BIRTH, SEX, RACE_CODE, ETHINICITY, '+
    'PROGRAM_START, PROGRAM_END FROM RESTORE.CLIENT WHERE (PROGRAM_START <  {d '2016-12-22'})'
  /ASSUMEDSTRWIDTH=255.
 
>Warning.  Command name: GET DATA
>SQLExecDirect failed :[IBM SPSS][ODBC Oracle Wire Protocol driver]Escape clause beginning with --*( or { was not properly ended with *)-- or }.
 
CACHE.
EXECUTE.

[Andy W]

Maybe try double quotes within the SQL statement (or vice versa double quotes outside the SQL statement.)

[jkpeck]

The syntax as written has single quotes inside single quotes, so it definitely will not work.  You can either use the other type of quotes on the outside, but depending on the database, you might need double quotes on the inside (and single outside).

There is a custom dialog box named quotetext that will take as input a SQL statement and write out the SQL properly wrapped and quoted for use inside Statistics.  You can get this from the SPSS Community site (www.ibm.com/developerworks/spssdevcentral) and install it from the Utilities menu.

[Rick Oliver]

My limited experience suggests that many databases will accept square brackets in place of quotes, and I just find that less error-prone when I write my own quoted SQL strings in SPSS Statistics. For example:

'SELECT fieldname1 [fieldname two] fieldname3 [fieldname four]'

On Wed, Sep 7, 2016 at 3:40 PM, jkpeck <[hidden email]> wrote:

The syntax as written has single quotes inside single quotes, so it
definitely will not work.  You can either use the other type of quotes on
the outside, but depending on the database, you might need double quotes on
the inside (and single outside).

There is a custom dialog box named quotetext that will take as input a SQL
statement and write out the SQL properly wrapped and quoted for use inside
Statistics.  You can get this from the SPSS Community site
(www.ibm.com/developerworks/spssdevcentral) and install it from the
Utilities menu.



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/SQL-Date-Query-Limit-Retrieved-Cases-tp5733067p5733069.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

[DKUKEC]

Thank you for your help... placing the double quotes on the outside and single quotes inside the SQL (  ) did the trick.