Python, macros, sql
Locked
 
|
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 [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 |
|
|
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:[hidden email]] On Behalf Of David Wright Sent: Wednesday, July 08, 2009 5:54 PM To: [hidden email] 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 [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 |
|
|
In reply to this post by wsu_wright
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:[hidden email]] On Behalf Of David Wright Sent: Wednesday, July 08, 2009 6:54 PM To: [hidden email] 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 [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 |
|
|
Hi,
To quickly quote sql syntax, you can paste your sql syntax in the first column in Excel and then type the following in the second column: ="'"&A1&"'" I find it very handy to quickly generate spss or other syntax using string concatenations in excel. Cheers!! Albert-Jan --- On Thu, 7/9/09, Peterson, Bruce L. <[hidden email]> wrote: > From: Peterson, Bruce L. <[hidden email]> > Subject: Re: Python, macros, sql > To: [hidden email] > Date: Thursday, July 9, 2009, 4:35 PM > 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:[hidden email]] > On Behalf Of David Wright > Sent: Wednesday, July 08, 2009 6:54 PM > To: [hidden email] > 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 > [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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |