Date:         Fri, 25 Feb 2005 07:54:48 -0600
Reply-To:     "Oliver, Richard" <richard@spss.com>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         "Oliver, Richard" <richard@spss.com>
Subject:      Re: Syntax problem
Comments: To: "Marks, Jim" <Jim.Marks@lodgenet.com>
Content-Type: text/plain; charset="us-ascii"

I think this incorrect. I think you need to use double quotes, not two sets of single quotes.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:SPSSX-L@LISTSERV.UGA.EDU] On Behalf Of Marks, Jim
Sent: Thursday, February 24, 2005 11:02 PM
To: SPSSX-L@LISTSERV.UGA.EDU
Subject: Re: Syntax problem

Alexa:

To use string values in a GET DATA command with SQL, you need to enclose the reference string in two single quotes. Try:

    'AND questiongroup.CodePrefix=''XO'' '

Notice this NOT the double quote (") but two single quotes ('').

--jim
-----Original Message-----
From: SPSSX(r) Discussion [mailto:SPSSX-L@LISTSERV.UGA.EDU]On Behalf Of Alex Davidson
Sent: Thursday, February 24, 2005 5:54 PM
To: SPSSX-L@LISTSERV.UGA.EDU
Subject: Syntax problem


I am having inexplicable problems with the attached syntax file with the line I've marked with an *.  'XO' is the name of a data column in the 'questiongroup.CodePrefix' table. The rest works fine, but this line keeps popping an error:
 >Warning.  Command name: GET DATA
>SQLExecDirect failed :You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use near 'AND questiongroup.CodePrefix=XO AND questiontype.OID = questions.OID_QuestionTyp' at line 1 Tried all the permutations of single and double inner and outer quotes for the line  - nought seems to work. I see in the manual that they suggest using back-quotes but that doesn't work either.
I'd be grateful for any suggestions.
GET DATA /TYPE=ODBC /CONNECT=
 'DSN=polsci;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=polsci;SERVER=polsci.smartsims.com; '
  'STMT=;'
 /SQL =
 'SELECT '
    '(SELECT Actors.Name FROM Actors WHERE Actors.OID=Session.OID_Actor) AS Coder, '
    'session.ItemTitle,'
    'questiongroup.CodePrefix, '
    'questions.CodeNumber, '
    'questions.ShortName, '
    'questions.Description,  '
    'questiontype.Name As TypeName,  '
    'questiongroup.Name AS GroupName,  '
    'actors.Name AS ActorName, '
    '(IF(QuestionType.Type=4,'
        '(SELECT RadioElement.Text FROM RadioElement '
        'WHERE RadioElement.OID_QuestionType=QuestionType.OID AND RadioElement.Code=answeritem.Value),'
        'answeritem.Value)) AS Value, '
    'Session.Created, Session.Duration '
  'FROM  questions, questiontype, questiongroup, answer, actors, answeritem, session '
  'WHERE '
             'Session.OID=answer.OID_Session '
     'AND Questions.CodeNumber=1 '
*    'AND questiongroup.CodePrefix='XO' '
     'AND questiontype.OID = questions.OID_QuestionType  '
     'AND questiongroup.OID = questions.OID_QuestionGroup  '
     'AND questions.OID = answer.OID_Question  '
     'AND answer.OID = answeritem.OID_Answer  '
     'AND actors.OID = answer.OID_Actor '
     'AND NOT actors.Deleted '
     'AND NOT questions.Deleted '
     'AND NOT Session.Deleted '
  'ORDER BY answer.OID_Session, questiongroup.CodePrefix, questions.CodeNumber'
 .
CACHE.
EXECUTE.

_________________________________________________
Dr Alex Davidson
Odyssey House
Private Bag MBE M230
AUCKLAND
ph. 623 1447 ext. 708