« Return to Thread: Python, macros, sql
Re: Python, macros, sql
by Peterson, Bruce L.
::
Rate this Message:
Hi David
Jon Peck's advice is exactly correct regarding the GET DATA / TYPE=ODBC command.
However, if you're willing to use an older command you can do what you want.
I use:
GET CAPTURE ODBC
/CONNECT=<connect string here>
/select a.*
from table1 a.
cache.
I develop all my SQL and initial passes at the data via the MS SQL Server Management Studio. Using the GET CAPTURE, you can just do a quick copy and paste of the SQL into the syntax and you can immediately run. There is no need to quote each line so productivity is very high.
Hope this helps.
Bruce
-----Original Message-----
From: SPSSX(r) Discussion [mailto:SPSSX-L@...] On Behalf Of David Wright
Sent: Wednesday, July 08, 2009 6:54 PM
To: SPSSX-L@...
Subject: 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
Jon Peck's advice is exactly correct regarding the GET DATA / TYPE=ODBC command.
However, if you're willing to use an older command you can do what you want.
I use:
GET CAPTURE ODBC
/CONNECT=<connect string here>
/select a.*
from table1 a.
cache.
I develop all my SQL and initial passes at the data via the MS SQL Server Management Studio. Using the GET CAPTURE, you can just do a quick copy and paste of the SQL into the syntax and you can immediately run. There is no need to quote each line so productivity is very high.
Hope this helps.
Bruce
-----Original Message-----
From: SPSSX(r) Discussion [mailto:SPSSX-L@...] On Behalf Of David Wright
Sent: Wednesday, July 08, 2009 6:54 PM
To: SPSSX-L@...
Subject: 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 |
