« Return to Thread: Python, macros, sql
Re: Python, macros, sql
by Peck, Jon
::
Rate this Message:
That is a misunderstanding. There is no need to quote field names unless they would syntactically invalid in SQL without the quotes. What you need to do is to quote each line so that the SPSS parser treats the entire line as a literal and passes it to the ODBC driver unchanged.
For example,
'SELECT T5.DEPTNO AS DEPTNO, T5.DNAME AS DNAME, T5.LOC '
'AS LOC FROM SCOTT.DEPT T5'
HTH,
Jon Peck
-----Original Message-----
From: SPSSX(r) Discussion [mailto:SPSSX-L@...] On Behalf Of David Wright
Sent: Wednesday, July 08, 2009 5:54 PM
To: SPSSX-L@...
Subject: [SPSSX-L] Python, macros, sql
We are currently migrating our data extraction queries from our current sql program to PASW. It appears that PASW requires the use of apostrophes (" ") on each factor within the sql statement (see below for an excerpt from the SPSS knowledge base on this issue). We have several complex sql statements that run multiple pages, inserting the apostrophes would be highly laborious and error prone.
Is there a way to use a macro or python to execute the sql statement as is (without the apostrophes on each factor) after the database connection is made?
David,
(from the SPSS knowledge base):
Using the wizard created syntax, delete everything after /SQL= . Then append your own SQL code. However, you will need to surround each line of SQL with apostrophes, as follows:
GET DATA /TYPE=ODBC /CONNECT=
'DSN=hqtechpdc;UID=SCOTT;PWD=#[''}"F,z-`'
/SQL =
'SELECT "T5"."DEPTNO" AS "DEPTNO", "T5"."DNAME" AS "DNAME", "T5"."LOC" '
'AS "LOC" FROM "SCOTT"."DEPT" "T5"'
=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (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
LISTSERV@... (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
For example,
'SELECT T5.DEPTNO AS DEPTNO, T5.DNAME AS DNAME, T5.LOC '
'AS LOC FROM SCOTT.DEPT T5'
HTH,
Jon Peck
-----Original Message-----
From: SPSSX(r) Discussion [mailto:SPSSX-L@...] On Behalf Of David Wright
Sent: Wednesday, July 08, 2009 5:54 PM
To: SPSSX-L@...
Subject: [SPSSX-L] Python, macros, sql
We are currently migrating our data extraction queries from our current sql program to PASW. It appears that PASW requires the use of apostrophes (" ") on each factor within the sql statement (see below for an excerpt from the SPSS knowledge base on this issue). We have several complex sql statements that run multiple pages, inserting the apostrophes would be highly laborious and error prone.
Is there a way to use a macro or python to execute the sql statement as is (without the apostrophes on each factor) after the database connection is made?
David,
(from the SPSS knowledge base):
Using the wizard created syntax, delete everything after /SQL= . Then append your own SQL code. However, you will need to surround each line of SQL with apostrophes, as follows:
GET DATA /TYPE=ODBC /CONNECT=
'DSN=hqtechpdc;UID=SCOTT;PWD=#[''}"F,z-`'
/SQL =
'SELECT "T5"."DEPTNO" AS "DEPTNO", "T5"."DNAME" AS "DNAME", "T5"."LOC" '
'AS "LOC" FROM "SCOTT"."DEPT" "T5"'
=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@... (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
LISTSERV@... (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
« Return to Thread: Python, macros, sql
| Free embeddable forum powered by Nabble | Forum Help |
