« Return to Thread: Python, macros, sql
Re: Python, macros, sql
by Albert-jan Roskam
::
Rate this Message:
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. <bpeterson@...> wrote:
> From: Peterson, Bruce L. <bpeterson@...>
> Subject: Re: Python, macros, sql
> To: SPSSX-L@...
> 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: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
>
=====================
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 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. <bpeterson@...> wrote:
> From: Peterson, Bruce L. <bpeterson@...>
> Subject: Re: Python, macros, sql
> To: SPSSX-L@...
> 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: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
>
=====================
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 |
